我是用 npgsql 连接 opengauss ，监控发现，登录 opengauss过程中，npgsql 会执行以下脚本从数据库服务器获取信息：
explain ANALYZE
SELECT ns.nspname, t.oid, t.typname, t.typtype, t.typnotnull, t.elemtypoid
FROM (
– Arrays have typtype=b - this subquery identifies them by their typreceive and converts their typtype to a
– We first do this for the type (innerest-most subquery), and then for its element type
– This also returns the array element, range subtype and domain base type as elemtypoid
SELECT
typ.oid, typ.typnamespace, typ.typname, typ.typtype, typ.typrelid, typ.typnotnull, typ.relkind,
elemtyp.oid AS elemtypoid, elemtyp.typname AS elemtypname, elemcls.relkind AS elemrelkind,
CASE WHEN elemproc.proname=‘array_recv’ THEN ‘a’ ELSE elemtyp.typtype END AS elemtyptype
FROM (
SELECT typ.oid, typnamespace, typname, typrelid, typnotnull, relkind, typelem AS elemoid,
CASE WHEN proc.proname=‘array_recv’ THEN ‘a’ ELSE typ.typtype END AS typtype,
CASE
WHEN proc.proname=‘array_recv’ THEN typ.typelem
WHEN typ.typtype=‘r’ THEN rngsubtype
WHEN typ.typtype='d' THEN typ.typbasetype
END AS elemtypoid
FROM pg_type AS typ
LEFT JOIN pg_class AS cls ON (cls.oid = typ.typrelid)
LEFT JOIN pg_proc AS proc ON proc.oid = typ.typreceive
LEFT JOIN pg_range ON (pg_range.rngtypid = typ.oid)
) AS typ
LEFT JOIN pg_type AS elemtyp ON elemtyp.oid = elemtypoid
LEFT JOIN pg_class AS elemcls ON (elemcls.oid = elemtyp.typrelid)
LEFT JOIN pg_proc AS elemproc ON elemproc.oid = elemtyp.typreceive
) AS t
JOIN pg_namespace AS ns ON (ns.oid = typnamespace)
WHERE
typtype IN ('b', 'r', 'm', 'e', 'd') OR -- Base, range, multirange, enum, domain
(typtype = 'c' AND relkind='c') OR -- User-defined free-standing composites (not table composites) by default
(typtype = 'p' AND typname IN ('record', 'void', 'unknown')) OR -- Some special supported pseudo-types
(typtype = 'a' AND ( -- Array of...
elemtyptype IN ('b', 'r', 'm', 'e', 'd') OR -- Array of base, range, multirange, enum, domain
(elemtyptype = 'p' AND elemtypname IN ('record', 'void')) OR -- Arrays of special supported pseudo-types
(elemtyptype = 'c' AND elemrelkind='c') -- Array of user-defined free-standing composites (not table composites) by default
))
ORDER BY CASE
WHEN typtype IN ('b', 'e', 'p') THEN 0 -- First base types, enums, pseudo-types
WHEN typtype = 'c' THEN 1 -- Composites after (fields loaded later in 2nd pass)
WHEN typtype = 'r' THEN 2 -- Ranges after
WHEN typtype = 'm' THEN 3 -- Multiranges after
WHEN typtype = 'd' AND elemtyptype <> 'a' THEN 4 -- Domains over non-arrays after
WHEN typtype = 'a' THEN 5 -- Arrays after
WHEN typtype = 'd' AND elemtyptype = 'a' THEN 6 -- Domains over arrays last
END;
连接数据库需要好长时间，以下是执行计划，如下表：
Sort (cost=59.21..59.22 rows=1 width=267) (actual time=76727.251..76727.263 rows=180 loops=1)
Sort Key: (CASE WHEN (CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ANY (‘{b,e,p}’::“char”)) THEN 0 WHEN (CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘c’::“char”) THEN 1 WHEN (CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘r’::“char”) THEN 2 WHEN (CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘m’::“char”) THEN 3 WHEN ((CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘d’::“char”) AND (CASE WHEN (elemproc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE elemtyp.typtype END <> ‘a’::“char”)) THEN 4 WHEN (CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘a’::“char”) THEN 5 WHEN ((CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘d’::“char”) AND (CASE WHEN (elemproc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE elemtyp.typtype END = ‘a’::“char”)) THEN 6 ELSE NULL::integer END)
Sort Method: quicksort Memory: 75kB
→ Nested Loop (cost=9.41..59.20 rows=1 width=267) (actual time=38051.889..76727.059 rows=180 loops=1)
→ Nested Loop Left Join (cost=9.41..50.87 rows=1 width=207) (actual time=38050.809..76725.241 rows=180 loops=1)
Filter: ((CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ANY (‘{b,r,m,e,d}’::“char”)) OR ((CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘c’::“char”) AND (cls.relkind = ‘c’::“char”)) OR ((CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘p’::“char”) AND (typ.typname = ANY (‘{record,void,unknown}’::name))) OR ((CASE WHEN (proc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE typ.typtype END = ‘a’::“char”) AND ((CASE WHEN (elemproc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE elemtyp.typtype END = ANY (‘{b,r,m,e,d}’::“char”)) OR ((CASE WHEN (elemproc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE elemtyp.typtype END = ‘p’::“char”) AND (elemtyp.typname = ANY (‘{record,void}’::name))) OR ((CASE WHEN (elemproc.proname = ‘array_recv’::name) THEN ‘a’::“char” ELSE elemtyp.typtype END = ‘c’::“char”) AND (elemcls.relkind = ‘c’::“char”)))))
Rows Removed by Filter: 785
→ Nested Loop Left Join (cost=9.41..42.54 rows=1 width=213) (actual time=38050.793..76722.621 rows=965 loops=1)
→ Hash Left Join (cost=9.41..34.26 rows=1 width=216) (actual time=38050.786..76721.247 rows=965 loops=1)
Hash Cond: (CASE WHEN (proc.proname = ‘array_recv’::name) THEN typ.typelem WHEN (typ.typtype = ‘r’::“char”) THEN pg_range.rngsubtype WHEN (typ.typtype = ‘d’::“char”) THEN typ.typbasetype ELSE NULL::oid END = elemtyp.oid)
→ Hash Left Join (cost=1.14..25.97 rows=1 width=151) (actual time=1.678..38671.524 rows=965 loops=1)
Hash Cond: (typ.oid = pg_range.rngtypid)
→ Nested Loop Left Join (cost=0.00..24.82 rows=1 width=147) (actual time=1.276..38670.722 rows=965 loops=1)
→ Nested Loop Left Join (cost=0.00..16.54 rows=1 width=87) (actual time=0.673..38649.806 rows=965 loops=1)
→ Index Scan using pg_type_oid_index on pg_type typ (cost=0.00..8.26 rows=1 width=90) (actual time=0.657..38625.535 rows=965 loops=1)
→ Index Scan using pg_class_oid_index on pg_class cls (cost=0.00..8.27 rows=1 width=5) (actual time=18.795..23.771 rows=737 loops=965)
Index Cond: (oid = typ.typrelid)
→ Index Scan using pg_proc_oid_index on pg_proc proc (cost=0.00..8.27 rows=1 width=68) (actual time=20.193..20.408 rows=946 loops=965)
Index Cond: (oid = (typ.typreceive)::oid)
→ Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.291..0.291 rows=6 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 257kB
→ Seq Scan on pg_range (cost=0.00..1.06 rows=6 width=8) (actual time=0.284..0.286 rows=6 loops=1)
→ Hash (cost=8.26..8.26 rows=1 width=77) (actual time=38048.996..38048.996 rows=965 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 362kB
→ Index Scan using pg_type_oid_index on pg_type elemtyp (cost=0.00..8.26 rows=1 width=77) (actual time=0.015..38048.707 rows=965 loops=1)
→ Index Scan using pg_class_oid_index on pg_class elemcls (cost=0.00..8.27 rows=1 width=5) (actual time=0.783..0.940 rows=33 loops=965)
Index Cond: (oid = elemtyp.typrelid)
→ Index Scan using pg_proc_oid_index on pg_proc elemproc (cost=0.00..8.27 rows=1 width=68) (actual time=1.474..1.499 rows=123 loops=965)
Index Cond: (oid = (elemtyp.typreceive)::oid)
→ Index Scan using pg_namespace_oid_index on pg_namespace ns (cost=0.00..8.27 rows=1 width=68) (actual time=1.426..1.479 rows=180 loops=180)
Index Cond: (oid = typ.typnamespace)
Total runtime: 76727.883 ms
从执行计划看，执行这个脚本需要 很长时间，表中所有查询都是基于系统表查询的，目前数据库连接需要5分钟以上，不知道开发团队有没有办法提供优化建议？