From: | regrog <andrea(dot)vencato(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | view reading information_schema is slow in PostgreSQL 12 |
Date: | 2020-06-12 15:21:18 |
Message-ID: | 1591975278775-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Imre Samu | 2020-06-12 19:00:43 | Re: view reading information_schema is slow in PostgreSQL 12 |
Previous Message | Mikkel Lauritsen | 2020-06-11 05:05:50 | Re: Windows slowness? |