这是一个带有其计划的请求。请求目标是列出一个表结构：索引、外键、列。
这个请求在 postgres 11 (50ms) 上很快，在 postgres 12 (500ms) 上很慢。对于这个请求，pg12 怎么可能比 pg11 慢 x10 倍？
请求代码：
EXPLAIN (ANALYZE, BUFFERS)
SELECT
"tableConstraints".constraint_name AS "constraintName",
"tableConstraints".table_name AS "tableName",
"tableConstraints".constraint_type AS "columnType",
"keyColumnUsage".column_name AS "columnName",
"constraintColumnUsage".table_name AS "foreignTableName",
"constraintColumnUsage".column_name AS "foreignColumnName",
json_agg("uidx"."uniqueIndexes") filter (where "uidx"."uniqueIndexes" is not null) AS "uniqueIndexes"
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS "tableConstraints"
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS "keyColumnUsage"
ON "tableConstraints".constraint_name = "keyColumnUsage".constraint_name
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS "constraintColumnUsage"
ON "constraintColumnUsage".constraint_name = "tableConstraints".constraint_name
FULL OUTER JOIN (
-- Get the index name, table name and list of columns of the unique indexes of a table
SELECT
pg_index.indexrelid::regclass AS "indexName",
"pgClass1".relname AS "tableName",
json_agg(DISTINCT pg_attribute.attname) AS "uniqueIndexes"
FROM
pg_class AS "pgClass1",
pg_class AS "pgClass2",
pg_index,
pg_attribute
WHERE "pgClass1".relname = 'projects'
AND "pgClass1".oid = pg_index.indrelid
AND "pgClass2".oid = pg_index.indexrelid
AND pg_attribute.attrelid = "pgClass1".oid
AND pg_attribute.attnum = ANY(pg_index.indkey)
AND not pg_index.indisprimary
AND pg_index.indisunique
AND "pgClass1".relkind = 'r'
AND not "pgClass1".relname like 'pg%'
GROUP BY
"tableName",
"indexName"
) AS "uidx"
ON "uidx"."tableName" = "tableConstraints".table_name
WHERE "uidx"."tableName" = 'projects'
OR "tableConstraints".table_name = 'projects'
GROUP BY
"constraintName",
"tableConstraints".table_name,
"columnType",
"columnName",
"foreignTableName",
"foreignColumnName"
这是计划的开始，对于消息来说太长了：
GroupAggregate (cost=380.04..380.05 rows=1 width=384) (actual time=194.087..194.124 rows=4 loops=1)
" Group Key: ""*SELECT* 1"".constraint_name, ""*SELECT* 1"".table_name, ""*SELECT* 1"".constraint_type, ((a.attname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".relname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".attname)::information_schema.sql_identifier)"
Buffers: shared hit=41399 read=36
I/O Timings: read=0.270
-> Sort (cost=380.04..380.05 rows=1 width=384) (actual time=194.072..194.103 rows=12 loops=1)
" Sort Key: ""*SELECT* 1"".constraint_name, ""*SELECT* 1"".table_name, ""*SELECT* 1"".constraint_type, ((a.attname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".relname)::information_schema.sql_identifier), ((""*SELECT* 1_1"".attname)::information_schema.sql_identifier)"
Sort Method: quicksort Memory: 31kB
Buffers: shared hit=41399 read=36
I/O Timings: read=0.270
-> Hash Full Join (cost=140.09..380.04 rows=1 width=384) (actual time=44.129..194.040 rows=12 loops=1)
" Hash Cond: ((""*SELECT* 1"".table_name)::name = uidx.""tableName"")"
" Filter: ((uidx.""tableName"" = 'projects'::name) OR ((""*SELECT* 1"".table_name)::name = 'projects'::name))"
Rows Removed by Filter: 110
Buffers: shared hit=41393 read=36
I/O Timings: read=0.270
-> Nested Loop (cost=129.73..369.69 rows=1 width=352) (actual time=5.651..193.250 rows=114 loops=1)
" Join Filter: (c.conname = (""*SELECT* 1"".constraint_name)::name)"
Rows Removed by Join Filter: 35454
Buffers: shared hit=41310 read=36
I/O Timings: read=0.270
-> Nested Loop (cost=102.68..195.28 rows=1 width=320) (actual time=3.427..7.064 rows=114 loops=1)
Buffers: shared hit=2369 read=17
I/O Timings: read=0.138
-> Hash Join (cost=102.62..194.97 rows=4 width=296) (actual time=3.411..6.310 rows=114 loops=1)
" Hash Cond: (c.conname = ""*SELECT* 1_1"".conname)"
Buffers: shared hit=2027 read=17
I/O Timings: read=0.138
-> ProjectSet (cost=24.55..56.88 rows=16000 width=341) (actual time=0.840..3.580 rows=110 loops=1)
Buffers: shared hit=290 read=2
I/O Timings: read=0.030
-> Nested Loop (cost=24.55..32.06 rows=16 width=95) (actual time=0.391..1.097 rows=108 loops=1)
Buffers: shared hit=253
-> Hash Join (cost=24.53..31.18 rows=16 width=99) (actual time=0.378..0.657 rows=108 loops=1)
Hash Cond: (r.relnamespace = nr.oid)
Buffers: shared hit=36
-> Hash Join (cost=23.49..30.12 rows=24 width=103) (actual time=0.239..0.452 rows=108 loops=1)
Hash Cond: (c.conrelid = r.oid)
Buffers: shared hit=27
-> Seq Scan on pg_constraint c (cost=0.00..6.55 rows=141 width=95) (actual time=0.012..0.114 rows=108 loops=1)
" Filter: (contype = ANY ('{p,u,f}'::""char""[]))"
Rows Removed by Filter: 10
Buffers: shared hit=6
-> Hash (cost=23.12..23.12 rows=105 width=12) (actual time=0.205..0.206 rows=108 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=21
-> Seq Scan on pg_class r (cost=0.00..23.12 rows=105 width=12) (actual time=0.008..0.185 rows=108 loops=1)
" Filter: (relkind = ANY ('{r,p}'::""char""[]))"
Rows Removed by Filter: 512
Buffers: shared hit=21
-> Hash (cost=1.02..1.02 rows=4 width=4) (actual time=0.123..0.124 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=9
-> Seq Scan on pg_namespace nr (cost=0.00..1.02 rows=4 width=4) (actual time=0.047..0.104 rows=5 loops=1)
Filter: (NOT pg_is_other_temp_schema(oid))
Rows Removed by Filter: 2
Buffers: shared hit=9
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nc (cost=0.03..0.06 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=108)
Index Cond: (oid = c.connamespace)
Heap Fetches: 108
Buffers: shared hit=217
-> Hash (cost=78.06..78.06 rows=4 width=192) (actual time=2.550..2.565 rows=124 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 36kB
Buffers: shared hit=1737 read=15
I/O Timings: read=0.108
-> Append (cost=34.79..78.06 rows=4 width=192) (actual time=0.522..2.503 rows=124 loops=1)
Buffers: shared hit=1737 read=15
I/O Timings: read=0.108
" -> Subquery Scan on ""*SELECT* 1_1"" (cost=34.79..34.79 rows=1 width=192) (actual time=0.521..0.538 rows=14 loops=1)"
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Unique (cost=34.79..34.79 rows=1 width=324) (actual time=0.520..0.534 rows=14 loops=1)
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Sort (cost=34.79..34.79 rows=1 width=324) (actual time=0.519..0.526 rows=28 loops=1)
" Sort Key: nr_1.nspname, r_1.relname, r_1.relowner, a_1.attname, nc_1.nspname, c_1.conname"
Sort Method: quicksort Memory: 39kB
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.20..34.78 rows=1 width=324) (actual time=0.178..0.489 rows=28 loops=1)
Join Filter: (c_1.connamespace = nc_1.oid)
Rows Removed by Join Filter: 140
Buffers: shared hit=281 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.20..33.74 rows=1 width=264) (actual time=0.173..0.431 rows=28 loops=1)
Buffers: shared hit=253 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.17..33.49 rows=1 width=204) (actual time=0.163..0.394 rows=28 loops=1)
Buffers: shared hit=197 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.11..33.42 rows=1 width=140) (actual time=0.154..0.336 rows=28 loops=1)
Buffers: shared hit=113 read=10
I/O Timings: read=0.071
-> Nested Loop (cost=0.06..30.91 rows=1 width=76) (actual time=0.103..0.216 rows=28 loops=1)
Buffers: shared hit=27 read=9
I/O Timings: read=0.061
-> Seq Scan on pg_constraint c_1 (cost=0.00..6.51 rows=6 width=72) (actual time=0.008..0.045 rows=10 loops=1)
" Filter: (contype = 'c'::""char"")"
Rows Removed by Filter: 108
Buffers: shared hit=6
-> Index Scan using pg_depend_depender_index on pg_depend d (cost=0.06..4.06 rows=1 width=12) (actual time=0.014..0.016 rows=3 loops=10)
Index Cond: ((classid = '2606'::oid) AND (objid = c_1.oid))
Filter: (refclassid = '1259'::oid)
Rows Removed by Filter: 0
Buffers: shared hit=21 read=9
I/O Timings: read=0.061
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1 (cost=0.06..2.51 rows=1 width=70) (actual time=0.004..0.004 rows=1 loops=28)
Index Cond: ((attrelid = d.refobjid) AND (attnum = d.refobjsubid))
Filter: (NOT attisdropped)
Buffers: shared hit=86 read=1
I/O Timings: read=0.010
-> Index Scan using pg_class_oid_index on pg_class r_1 (cost=0.06..0.07 rows=1 width=76) (actual time=0.002..0.002 rows=1 loops=28)
Index Cond: (oid = a_1.attrelid)
" Filter: ((relkind = ANY ('{r,p}'::""char""[])) AND pg_has_role(relowner, 'USAGE'::text))"
Buffers: shared hit=84
-> Index Scan using pg_namespace_oid_index on pg_namespace nr_1 (cost=0.03..0.20 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=28)
Index Cond: (oid = r_1.relnamespace)
Buffers: shared hit=56
-> Seq Scan on pg_namespace nc_1 (cost=0.00..1.02 rows=6 width=68) (actual time=0.000..0.001 rows=6 loops=28)
Buffers: shared hit=28
" -> Subquery Scan on ""*SELECT* 2_1"" (cost=23.66..43.26 rows=3 width=192) (actual time=0.315..1.950 rows=110 loops=1)"
Buffers: shared hit=1456 read=5
I/O Timings: read=0.038
-> Nested Loop (cost=23.66..43.25 rows=3 width=324) (actual time=0.314..1.935 rows=110 loops=1)
Buffers: shared hit=1456 read=5
I/O Timings: read=0.038
-> Nested Loop (cost=23.63..43.06 rows=3 width=196) (actual time=0.303..1.819 rows=110 loops=1)
Buffers: shared hit=1235 read=5
I/O Timings: read=0.038
-> Nested Loop (cost=23.61..42.59 rows=3 width=200) (actual time=0.291..1.628 rows=110 loops=1)
Join Filter: (r_2.oid = a_2.attrelid)
Buffers: shared hit=1014 read=5
I/O Timings: read=0.038
-> Hash Join (cost=23.55..30.18 rows=8 width=195) (actual time=0.249..0.318 rows=108 loops=1)
" Hash Cond: (CASE c_2.contype WHEN 'f'::""char"" THEN c_2.confrelid ELSE c_2.conrelid END = r_2.oid)"
Buffers: shared hit=46
-> Seq Scan on pg_constraint c_2 (cost=0.00..6.55 rows=141 width=123) (actual time=0.004..0.031 rows=108 loops=1)
" Filter: (contype = ANY ('{p,u,f}'::""char""[]))"
Rows Removed by Filter: 10
Buffers: shared hit=6
-> Hash (cost=23.43..23.43 rows=35 width=72) (actual time=0.224..0.225 rows=38 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
Buffers: shared hit=40
-> Seq Scan on pg_class r_2 (cost=0.00..23.43 rows=35 width=72) (actual time=0.072..0.214 rows=38 loops=1)
" Filter: ((relkind = ANY ('{r,p}'::""char""[])) AND pg_has_role(relowner, 'USAGE'::text))"
Rows Removed by Filter: 582
Buffers: shared hit=40
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_2 (cost=0.06..1.55 rows=1 width=70) (actual time=0.005..0.012 rows=1 loops=108)
" Index Cond: (attrelid = CASE c_2.contype WHEN 'f'::""char"" THEN c_2.confrelid ELSE c_2.conrelid END)"
" Filter: ((NOT attisdropped) AND (attnum = ANY (CASE c_2.contype WHEN 'f'::""char"" THEN c_2.confkey ELSE c_2.conkey END)))"
Rows Removed by Filter: 25
Buffers: shared hit=968 read=5
I/O Timings: read=0.038
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nr_2 (cost=0.03..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=110)
Index Cond: (oid = r_2.relnamespace)
Heap Fetches: 110
Buffers: shared hit=221
-> Index Only Scan using pg_namespace_oid_index on pg_namespace nc_2 (cost=0.03..0.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=110)
Index Cond: (oid = c_2.connamespace)
Heap Fetches: 110
Buffers: shared hit=221
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.06..0.08 rows=1 width=70) (actual time=0.005..0.005 rows=1 loops=114)
Index Cond: ((attrelid = r.oid) AND (attnum = ((information_schema._pg_expandarray(c.conkey))).x))
" Filter: ((NOT attisdropped) AND (pg_has_role(r.relowner, 'USAGE'::text) OR has_column_privilege(r.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))"
Buffers: shared hit=342
-> Append (cost=27.05..173.97 rows=124 width=160) (actual time=0.072..1.605 rows=312 loops=114)
Buffers: shared hit=38941 read=19
I/O Timings: read=0.132
" -> Subquery Scan on ""*SELECT* 1"" (cost=27.05..35.65 rows=12 width=160) (actual time=0.072..0.317 rows=116 loops=114)"
Buffers: shared hit=28556 read=8
I/O Timings: read=0.062
-> Nested Loop (cost=27.05..35.61 rows=12 width=512) (actual time=0.072..0.305 rows=116 loops=114)
Buffers: shared hit=28556 read=8
I/O Timings: read=0.062
-> Nested Loop (cost=27.03..34.94 rows=12 width=133) (actual time=0.069..0.195 rows=116 loops=114)
Join Filter: (r_3.relnamespace = nr_3.oid)
Rows Removed by Join Filter: 464
Buffers: shared hit=2107 read=8
I/O Timings: read=0.062
-> Seq Scan on pg_namespace nr_3 (cost=0.00..1.02 rows=4 width=4) (actual time=0.001..0.004 rows=5 loops=114)
Filter: (NOT pg_is_other_temp_schema(oid))
Rows Removed by Filter: 2
Buffers: shared hit=114
-> Materialize (cost=27.03..33.64 rows=18 width=137) (actual time=0.004..0.010 rows=116 loops=570)
Buffers: shared hit=1993 read=8
I/O Timings: read=0.062
-> Hash Join (cost=27.03..33.62 rows=18 width=137) (actual time=2.042..2.092 rows=116 loops=1)
Hash Cond: (c_3.conrelid = r_3.oid)
Buffers: shared hit=1993 read=8
I/O Timings: read=0.062
-> Seq Scan on pg_constraint c_3 (cost=0.00..6.51 rows=147 width=73) (actual time=0.005..0.031 rows=118 loops=1)
" Filter: (contype <> ALL ('{t,x}'::""char""[]))"
Buffers: shared hit=6
-> Hash (cost=26.77..26.77 rows=74 width=72) (actual time=2.006..2.007 rows=38 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
Buffers: shared hit=1987 read=8
I/O Timings: read=0.062
-
[content dropped]
-> Nested Loop (cost=0.20..10.34 rows=1 width=132) (actual time=0.506..0.599 rows=3 loops=1)
" Join Filter: (""pgClass1"".oid = pg_attribute.attrelid)"
Buffers: shared hit=80
-> Nested Loop (cost=0.14..9.13 rows=1 width=103) (actual time=0.054..0.071 rows=3 loops=1)
Buffers: shared hit=17
-> Nested Loop (cost=0.08..9.02 rows=1 width=103) (actual time=0.039..0.046 rows=3 loops=1)
Buffers: shared hit=7
" -> Index Scan using pg_class_relname_nsp_index on pg_class ""pgClass1"" (cost=0.06..4.06 rows=1 width=68) (actual time=0.012..0.014 rows=1 loops=1)"
Index Cond: (relname = 'projects'::name)
" Filter: ((relname !~~ 'pg%'::text) AND (relkind = 'r'::""char""))"
Buffers: shared hit=3
-> Index Scan using pg_index_indrelid_index on pg_index (cost=0.03..4.96 rows=1 width=35) (actual time=0.024..0.028 rows=3 loops=1)
" Index Cond: (indrelid = ""pgClass1"".oid)"
Filter: ((NOT indisprimary) AND indisunique)
Rows Removed by Filter: 4
Buffers: shared hit=4
" -> Index Only Scan using pg_class_oid_index on pg_class ""pgClass2"" (cost=0.06..0.10 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=3)"
Index Cond: (oid = pg_index.indexrelid)
Heap Fetches: 3
Buffers: shared hit=10
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=0.06..1.21 rows=1 width=70) (actual time=0.153..0.174 rows=1 loops=3)
Index Cond: (attrelid = pg_index.indrelid)
Filter: (attnum = ANY ((pg_index.indkey)::smallint[]))
Rows Removed by Filter: 65
Buffers: shared hit=63
Planning Time: 20.217 ms
Execution Time: 195.657 ms
@瘦
我能够在我拥有的庞大数据库上重现您的问题。
在 pg11 上，您的请求在一张桌子上给了我大约 50 毫秒的响应时间。在 pg12 上，相同的请求大约是 500 毫秒（x10 次！）
查看postgres 12 更改日志：
在我这边反省你的请求似乎表明请求的大部分成本来自这个 JOIN
但是，我远不是 SQL 专家，而是遵循更改日志，强制它使用默认排序规则，像这样
似乎回馈了我使用 pg11 获得的约 50 毫秒。
我仍然相信@jjanes 关于重写此请求以使用
pg_catalog表而不是information_schema视图的评论仍然对性能问题有益。