Hello,
I've noticed a ~50x regression in execution time for a query when moving
from Postgres 11.6 to 12.1. Here's an example:
SELECT tc.table_name, kcu.column_name, ccu.table_name AS
foreign_table_name, ccu.column_name AS foreign_column_name FROM
information_schema.table_constraints tc JOIN
information_schema.key_column_usage kcu ON tc.constraint_name =
kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON
ccu.constraint_name = tc.constraint_name WHERE tc.constraint_type =
'FOREIGN KEY' AND ccu.table_name = 'patient' ORDER BY tc.table_name,
kcu.column_name, ccu.table_name, ccu.column_name;
The only parameter to the query is the table name, in this case 'patient'.
My schema has maybe 50 tables and no table has more than 50 columns. Most
tables have around one to three foreign keys.
I did as straightforward a pg_upgrade as I could, so I don't know what the
difference there would be.
Insight much appreciated. My thought is this is a large difference in
execution time, and I'd like to know if I can get that time back. Anyway, I
could move toward caching the results of these queries, so it's not the
worst thing that could've happened. Overall 12.1 is looking like a godsend
over 11.6 for many other use cases I have!
PostgreSQL 11.6 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM
version 8.1.0 (clang-802.0.42), 64-bit
PostgreSQL 12.1 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM
version 8.1.0 (clang-802.0.42), 64-bit
Here's the ANALYZE output (from 11.6 then 12.1):
Sort (cost=1370.57..1370.59 rows=8 width=128) (actual time=10.416..10.417
rows=9 loops=1)
Sort Key: "*SELECT* 1".table_name,
((a.attname)::information_schema.sql_identifier), (("*SELECT*
1_1".attname)::information_schema.sql_identifier)
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=418.80..1370.45 rows=8 width=128) (actual
time=7.262..10.384 rows=9 loops=1)
-> Hash Join (cost=418.52..1247.29 rows=370 width=200) (actual
time=7.242..10.300 rows=9 loops=1)
Hash Cond:
(((c.conname)::information_schema.sql_identifier)::text = ("*SELECT*
1".constraint_name)::text)
-> ProjectSet (cost=30.87..254.69 rows=37000 width=341)
(actual time=0.612..5.179 rows=266 loops=1)
-> Hash Join (cost=30.87..60.26 rows=37 width=95)
(actual time=0.439..1.156 rows=256 loops=1)
Hash Cond: (c.connamespace = nc.oid)
-> Hash Join (cost=29.74..58.96 rows=37
width=99) (actual time=0.404..0.957 rows=256 loops=1)
Hash Cond: (r.relnamespace = nr.oid)
-> Hash Join (cost=28.61..57.59 rows=55
width=103) (actual time=0.379..0.779 rows=256 loops=1)
Hash Cond: (c.conrelid = r.oid)
-> Seq Scan on pg_constraint c
(cost=0.00..28.32 rows=252 width=95) (actual time=0.011..0.174 rows=256
loops=1)
Filter: (contype = ANY
('{p,u,f}'::"char"[]))
Rows Removed by Filter: 63
-> Hash (cost=27.07..27.07
rows=123 width=12) (actual time=0.359..0.359 rows=124 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 14kB
-> Seq Scan on pg_class r
(cost=0.00..27.07 rows=123 width=12) (actual time=0.008..0.307 rows=124
loops=1)
Filter: (relkind = ANY
('{r,p}'::"char"[]))
Rows Removed by Filter:
447
-> Hash (cost=1.07..1.07 rows=4 width=4)
(actual time=0.018..0.019 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 9kB
-> Seq Scan on pg_namespace nr
(cost=0.00..1.07 rows=4 width=4) (actual time=0.006..0.013 rows=5 loops=1)
Filter: (NOT
pg_is_other_temp_schema(oid))
Rows Removed by Filter: 4
-> Hash (cost=1.06..1.06 rows=6 width=4)
(actual time=0.027..0.027 rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
9kB
-> Seq Scan on pg_namespace nc
(cost=0.00..1.06 rows=6 width=4) (actual time=0.012..0.015 rows=9 loops=1)
-> Hash (cost=387.62..387.62 rows=2 width=256) (actual
time=4.938..4.938 rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Hash Join (cost=113.20..387.62 rows=2 width=256)
(actual time=2.928..4.925 rows=9 loops=1)
Hash Cond: (("*SELECT* 1".constraint_name)::text
= (("*SELECT* 1_1".conname)::information_schema.sql_identifier)::text)
