> view reading information_schema is slow in PostgreSQL 12
Hi,
What is the PG version?
IF PG < 12.3 THEN maybe related to this ?
https://www.postgresql.org/docs/release/12.3/ ( Repair performance regression in information_schema.triggers view )
Imre
regrog <andrea.vencato@xxxxxxxxx> ezt írta (időpont: 2020. jún. 12., P, 20:26):
I'm facing performance issues migrating from postgres 10 to 12 (also from 11
to 12) even with a new DB.
Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12.
I have a view that abstracts the data in the database:
CREATE OR REPLACE VIEW public.my_constraints
AS SELECT lower(tc.constraint_name) AS constraint_name,
tc.constraint_type,
tc.table_schema,
lower(tc.table_name) AS table_name,
lower(kcu.column_name) AS column_name,
ccu.table_schema AS reference_table_schema,
lower(ccu.table_name) AS reference_table_name,
lower(ccu.column_name) AS reference_column_name,
rc.update_rule,
rc.delete_rule
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu ON
tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema =
kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc ON
tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema =
rc.constraint_schema AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu ON
rc.unique_constraint_catalog = ccu.constraint_catalog AND
rc.unique_constraint_schema = ccu.constraint_schema AND
rc.unique_constraint_name = ccu.constraint_name
WHERE tc.constraint_catalog = 'my_catalog' AND tc.constraint_schema =
'public' AND tc.constraint_type <> 'CHECK';
The simple query: select * from my_constraints is normal but as soon as I
add where constraint_type = 'FOREIGN KEY' it takes a lot of time.
I don't have data in my tables at the moment, I have around 600 tables in my
schema.
I've analyzed the query but can't figure out what's wrong, this is the query
with the filter without the view:
select * from (SELECT lower(tc.constraint_name) AS constraint_name,
tc.constraint_type,
tc.table_schema,
lower(tc.table_name) AS table_name,
lower(kcu.column_name) AS column_name,
ccu.table_schema AS reference_table_schema,
lower(ccu.table_name) AS reference_table_name,
lower(ccu.column_name) AS reference_column_name,
rc.update_rule,
rc.delete_rule
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu ON
tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema =
kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc ON
tc.constraint_catalog = rc.constraint_catalog AND tc.constraint_schema =
rc.constraint_schema AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu ON
rc.unique_constraint_catalog = ccu.constraint_catalog AND
rc.unique_constraint_schema = ccu.constraint_schema AND
rc.unique_constraint_name = ccu.constraint_name
WHERE tc.constraint_catalog = 'my_catalog' AND tc.constraint_schema =
'public' AND tc.constraint_type <> 'CHECK'
) as a
where constraint_type = 'FOREIGN KEY'
postgres 10 plan
https://explain.depesz.com/s/mEmv
postgres 12 plan
https://explain.depesz.com/s/lovP
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
