Date: Fri, 12 Jun 2020 08:21:18 -0700 (MST)
From: regrog <andrea.vencato@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1591975278775-0.post@n3.nabble.com>
Subject: view reading information_schema is slow in PostgreSQL 12
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

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