sql - Pgsql query speed slow

Question

When I am doing a simple query

SELECT * FROM public.fa_shopro_order where user_id in(7351)

using pgsql, the log shows as the followings:

[2021-05-20 10:13:11.248][gp-2ze6fj026w0b11se7o.gpdb.rds.aliyuncs.com][48105][PGSQL][]
SELECT * FROM public.fa_shopro_order where user_id in(7351)

[2021-05-20 10:13:11.323][gp-2ze6fj026w0b11se7o.gpdb.rds.aliyuncs.com][48759][PGSQL][]
SELECT c.conkey FROM pg_constraint c WHERE c.contype = 'p' and c.conrelid = 55181996

[2021-05-20 10:13:11.329][gp-2ze6fj026w0b11se7o.gpdb.rds.aliyuncs.com][48759][PGSQL][]
SELECT nsp.nspname, cl.relname, array_upper(conkey,1) as pkeycount FROM pg_class cl LEFT JOIN pg_namespace nsp ON nsp.oid = cl.relnamespace LEFT JOIN pg_constraint con ON con.conrelid = cl.oid WHERE con.contype = 'p' AND cl.oid = 55181996

[2021-05-20 10:13:11.339][gp-2ze6fj026w0b11se7o.gpdb.rds.aliyuncs.com][48759][PGSQL][]
SELECT attname AS name, attrelid AS tid,coalesce((SELECT attnum = ANY (conkey) FROM pg_constraint WHERE contype = 'p' AND conrelid = attrelid), false) AS primarykey,not(attnotnull) AS allownull,(SELECT seq.oid FROM pg_class seq LEFT JOIN pg_depend dep ON seq.oid = dep.objid WHERE seq.relkind = 'S'::char AND dep.refobjsubid = attnum AND dep.refobjid = attrelid) IS NOT NULL AS autoincrement FROM pg_attribute WHERE attisdropped = false AND attrelid = (SELECT tbl.oid FROM pg_class tbl LEFT JOIN pg_namespace sch ON tbl.relnamespace = sch.oid WHERE tbl.relkind = 'r'::"char" AND tbl.relname = 'fa_shopro_order' AND sch.nspname = 'public') AND (attname = 'id' OR attname = 'type' OR attname = 'order_sn' OR attname = 'user_id' OR attname = 'activity_type' OR attname = 'goods_amount' OR attname = 'dispatch_amount' OR attname = 'phone' OR attname = 'consignee' OR attname = 'province_name' OR attname = 'city_name' OR attname = 'area_name' OR attname = 'address' OR attname = 'province_id' OR attname = 'city_id' OR attname = 'area_id' OR attname = 'status' OR attname = 'memo' OR attname = 'remark' OR attname = 'total_amount' OR attname = 'score_amount' OR attname = 'total_fee' OR attname = 'discount_fee' OR attname = 'coupon_fee' OR attname = 'pay_fee' OR attname = 'score_fee' OR attname = 'goods_original_amount' OR attname = 'coupons_id' OR attname = 'transaction_id' OR attname = 'payment_json' OR attname = 'pay_type' OR attname = 'paytime' OR attname = 'ext' OR attname = 'platform' OR attname = 'createtime' OR attname = 'updatetime' OR attname = 'deletetime' OR attname = 'admin_id' OR attname = 'pay_sn')

[2021-05-20 10:13:18.88][gp-2ze6fj026w0b11se7o.gpdb.rds.aliyuncs.com][48759][PGSQL][]
SELECT cl.column_name, ty.typtype, cl.udt_schema, ty.typname, cl.column_default FROM information_schema.columns cl LEFT JOIN pg_type ty ON ty.typname = cl.udt_name LEFT JOIN pg_namespace nsp ON nsp.oid = ty.typnamespace WHERE nsp.nspname = cl.udt_schema AND cl.table_schema = 'public' AND cl.table_name = 'fa_shopro_order'

Why I wrote one SQL query while the log returns multiple lines?

The running time shows that it took more than 3 seconds for such a simple query. Any solution is appreciated. Btw, when using join, it seems to be way faster.

标签： sqlpostgresqlquery-optimization