> 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.venc...@gmail.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 > > >
