Re: view reading information_schema is slow in PostgreSQL 12

> 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(dot)vencato(at)gmail(dot)com> 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
>
>
>