Aqui está um pedido com seu plano . O objetivo da solicitação é listar uma estrutura de tabela: índices, chaves estrangeiras, colunas.
Essa solicitação é rápida com postgres 11 (50ms) e lenta no postgres 12 (500ms). Como é possível que pg12 seja x10 vezes mais lento que pg11 para esta solicitação?
Código do pedido:
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"
Aqui está o início do plano, que é muito longo para a mensagem:
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
@Afinar
Consigo reproduzir seu problema em um banco de dados enorme que tenho.
Na página 11, sua solicitação me dá um tempo de resposta de cerca de 50ms em uma única tabela. Na página 12, a mesma solicitação é ~500ms (x10 vezes!)
Olhando para o changelog do postgres 12 :
Analisar sua solicitação do meu lado parece mostrar que a maior parte do custo da solicitação vem deste JOIN
Estou longe de ser um especialista em SQL, no entanto, e seguir o changelog, forçando-o a usar o agrupamento padrão assim
parece devolver os ~ 50ms que eu estava recebendo com a pg11.
Ainda estou convencido de que o comentário do @jjanes sobre reescrever essa solicitação para usar
pg_catalogtabelas em vez deinformation_schemavisualizações ainda seria benéfico para questões de desempenho.