Thread: view reading information_schema is slow in PostgreSQL 12

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

regrog <andrea.vencato@gmail.com> writes:
> I'm facing performance issues migrating from postgres 10 to 12 (also from 11
> to 12) even with a new DB.
> 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 looked at this a bit.  I see what's going on, but I don't see an easy
workaround :-(.  The information_schema.table_constraints view contains
a UNION ALL, which in your v10 query produces this part of the plan:

                  ->  Append  (cost=0.29..1127.54 rows=316 width=192) (actual time=0.068..11.116 rows=1839 loops=1)
                        ->  Subquery Scan on "*SELECT* 1"  (cost=0.29..226.26 rows=1 width=192) (actual
time=0.068..10.952rows=1839 loops=1) 
                              ->  Result  (cost=0.29..226.25 rows=1 width=288) (actual time=0.067..10.707 rows=1839
loops=1)
                                    One-Time Filter: (((current_database())::information_schema.sql_identifier)::text =
'testzeal'::text)
                                    ->  Nested Loop  (cost=0.29..226.25 rows=1 width=288) (actual time=0.055..10.454
rows=1839loops=1) 
                                          ...
                        ->  Subquery Scan on "*SELECT* 2"  (cost=1.44..901.27 rows=315 width=192) (actual
time=0.001..0.001rows=0 loops=1) 
                              ->  Result  (cost=1.44..898.12 rows=315 width=288) (actual time=0.001..0.001 rows=0
loops=1)
                                    One-Time Filter: (((('CHECK'::character
varying)::information_schema.character_data)::text<> 'CHECK'::text) AND
(((current_database())::information_schema.sql_identifier)::text= 'testzeal'::text) AND ((('CHECK' (...) 
                                    ->  Nested Loop  (cost=1.44..898.12 rows=315 width=288) (never executed)
                                          ...

The first clause in that "One-Time Filter" arises from your view's
"tc.constraint_type <> 'CHECK'" condition.  It's obviously constant-false,
but the v10 planner can't quite prove that because of the domain cast
that's in the way.  So the second arm of the UNION doesn't contribute any
actual result rows, but nonetheless it adds 315 rows to the estimated
output of the Append.  In v12, this same UNION produces just this:

      ->  Subquery Scan on "*SELECT* 1"  (cost=0.29..199.30 rows=1 width=352) (actual time=0.382..45.343 rows=1848
loops=1)
            ->  Result  (cost=0.29..199.29 rows=1 width=512) (actual time=0.381..44.384 rows=1848 loops=1)
                  One-Time Filter: (((current_database())::information_schema.sql_identifier)::text = 'testzeal'::text)
                  ->  Nested Loop  (cost=0.29..199.28 rows=1 width=257) (actual time=0.376..40.953 rows=1848 loops=1)
                        ...

The v12 planner is able to see through the domain cast, prove that
'CHECK' <> 'CHECK' is constant false, and thereby toss the entire second
half of the UNION as being a no-op.  Great work!  Except that now, the
estimated output rowcount is just one row not 316, which causes the
entire shape of the surrounding plan to change, to a form that is pretty
awful when the output rowcount is actually 1800-some.  The rowcount
estimates for the two UNION arms were just as lousy in v10, but it quite
accidentally fell into an overall estimate that was at least within an
order of magnitude of reality, allowing it to produce an overall plan
that didn't suck.

To get a decent plan out of v12, the problem is to get it to produce
a better rowcount estimate for the first arm of table_constraints'
UNION.  We don't necessarily need it to match the 1800 reality, but
we need it to be more than 1.  Unfortunately there's no simple way
to affect that.  The core misestimate is here:

                ->  Seq Scan on pg_constraint c_1  (cost=0.00..192.60 rows=14 width=73) (actual time=0.340..3.962
rows=1848loops=1) 
                      Filter: ((contype <> ALL ('{t,x}'::"char"[])) AND ((CASE contype WHEN 'c'::"char" THEN
'CHECK'::textWHEN 'f'::"char" THEN 'FOREIGN KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char" THEN
'UNIQUE'::textELSE NULL::text END)::text <> 'CHECK'::text) AND ((CASE contype WHEN 'c'::"char" THEN 'CHECK'::text WHEN
'f'::"char"THEN 'FOREIGN KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char" THEN 'UNIQUE'::text ELSE
NULL::textEND)::text = 'FOREIGN KEY'::text)) 
                      Rows Removed by Filter: 1052

I expect you're getting a fairly decent estimate for the "contype <>
ALL" condition, but the planner has no idea what to make of the CASE
construct, so it just falls back to a hard-wired default estimate.

I don't have any good suggestions at the moment.  If you had a lot more
tables (hence more rows in pg_constraint) the plan would likely shift
to something tolerable even with the crummy selectivity estimate for the
CASE.  But where you are, it's hard.  A conceivable workaround is to
drop the "tc.constraint_type <> 'CHECK'" condition from your view, which
would resurrect that UNION arm and probably get you back to something
similar to the v10 plan.

            regards, tom lane

On Sat, 13 Jun 2020 at 06:26, regrog <andrea.vencato@gmail.com> wrote:
>
> 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.

This appears to be down to bad statistics that cause pg12 to choose a
nested loop plan.  The pg12 plan has:

->  Hash Join  (cost=1281.91..2934.18 rows=68 width=192) (actual
time=0.024..21.915 rows=3538 loops=1848)"

on the inner side of a nested loop. 21.915 * 1848 loops is 40498.92
ms, so most of the time.

This comes down to the difference caused by 04fe805a17, where after
that commit we don't bother looking at the NOT NULL constraints in
table_constraints.

explain select * from (select * from
information_schema.table_constraints) c where constraint_type <>
'CHECK';

If you execute the above on both instances, you'll see PG12 does not
do an Append. PG10 does. Which results in more rows being estimated
and the planner choosing something better than a nested loop join.

You could try: SET enable_nestloop TO off;

I'm not really sure there's much you could do to improve the
statistics on the catalogue tables.

Alternatively, you could write a view based directly on the base
tables, bypassing information_schema completely.

David

On Sat, 13 Jun 2020 at 15:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I expect you're getting a fairly decent estimate for the "contype <>
> ALL" condition, but the planner has no idea what to make of the CASE
> construct, so it just falls back to a hard-wired default estimate.

This feels quite similar to [1].

I wondered if it would be more simple to add some smarts to look a bit
deeper into case statements for selectivity estimation purposes. An
OpExpr like:

CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN
'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK';

could be simplified to c.contype = 'c',  which we should have
statistics for. There'd certainly be case statement forms that
couldn't be simplified, but I think this one could.

David

[1]
https://www.postgresql.org/message-id/flat/CAApHDvr%2B6%3D7SZBAtesEavgOQ0ZC03syaRQk19E%2B%2BpiWLopTRbg%40mail.gmail.com#3ec465f343f1204446941df29fc9e715

David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, 13 Jun 2020 at 15:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I expect you're getting a fairly decent estimate for the "contype <>
>> ALL" condition, but the planner has no idea what to make of the CASE
>> construct, so it just falls back to a hard-wired default estimate.

> This feels quite similar to [1].

Yeah, it's the same thing.  As I commented in that thread, I'd seen
applications of the idea in information_schema views -- it's the
same principle of a view exposing a CASE construct that translates
a catalog column to what the SQL spec says should be returned, and
then the calling query trying to constrain that output.

> I wondered if it would be more simple to add some smarts to look a bit
> deeper into case statements for selectivity estimation purposes. An
> OpExpr like:
> CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN
> 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK';

Hm.  Maybe we could reasonably assume that the equality operators used
for such constructs are error-and-side-effect-free, thus dodging the
semantic problem I mentioned in the other thread?

            regards, tom lane

On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
> regrog <andrea.vencato@gmail.com> writes:
> > I'm facing performance issues migrating from postgres 10 to 12 (also from 11
> > to 12) even with a new DB.
> > 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 looked at this a bit.  I see what's going on, but I don't see an easy
> workaround :-(.  The information_schema.table_constraints view contains
> a UNION ALL, which in your v10 query produces this part of the plan:

> To get a decent plan out of v12, the problem is to get it to produce
> a better rowcount estimate for the first arm of table_constraints'
> UNION.  We don't necessarily need it to match the 1800 reality, but
> we need it to be more than 1.  Unfortunately there's no simple way
> to affect that.  The core misestimate is here:

> I expect you're getting a fairly decent estimate for the "contype <>
> ALL" condition, but the planner has no idea what to make of the CASE
> construct, so it just falls back to a hard-wired default estimate.
> 
> I don't have any good suggestions at the moment.  If you had a lot more
> tables (hence more rows in pg_constraint) the plan would likely shift
> to something tolerable even with the crummy selectivity estimate for the
> CASE.  But where you are, it's hard.  A conceivable workaround is to
> drop the "tc.constraint_type <> 'CHECK'" condition from your view, which
> would resurrect that UNION arm and probably get you back to something
> similar to the v10 plan.

For the purposes of making this work for v12, you might try to look at either a
temporary table:

CREATE TEMP TABLE constraints AS SELECT * FROM information_schema.table_constraints WHERE constraint_type='FOREIGN
KEY';
ANALYZE constraints;
SELECT * FROM ... LEFT JOIN constraints LEFT JOIN ...

or a CTE (which, if it works, is mostly dumb luck):
WITH constraints AS MATERIALIZED (SELECT * FROM information_schema.table_constraints) SELECT * FROM constraints WHERE
constraint_type='FOREIGNKEY';
 

Or make a copy of the system view with hacks for the worst misestimates (like
contype<>'c' instead of constraint_type<>'CHECK').

-- 
Justin

On Sat, 13 Jun 2020 at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.com> writes:
> > I wondered if it would be more simple to add some smarts to look a bit
> > deeper into case statements for selectivity estimation purposes. An
> > OpExpr like:
> > CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN
> > 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK';
>
> Hm.  Maybe we could reasonably assume that the equality operators used
> for such constructs are error-and-side-effect-free, thus dodging the
> semantic problem I mentioned in the other thread?

I'm only really talking about selectivity estimation only for now.
I'm not really sure why we'd need to ensure that the equality operator
is error and side effect free.  We'd surely only be executing the case
statement's operator's oprrest function?  We'd need to ensure we don't
invoke any casts that could error out.

David

On Sat, 13 Jun 2020 at 19:52, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Sat, 13 Jun 2020 at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > David Rowley <dgrowleyml@gmail.com> writes:
> > > I wondered if it would be more simple to add some smarts to look a bit
> > > deeper into case statements for selectivity estimation purposes. An
> > > OpExpr like:
> > > CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN
> > > 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK';
> >
> > Hm.  Maybe we could reasonably assume that the equality operators used
> > for such constructs are error-and-side-effect-free, thus dodging the
> > semantic problem I mentioned in the other thread?
>
> I'm only really talking about selectivity estimation only for now.
> I'm not really sure why we'd need to ensure that the equality operator
> is error and side effect free.  We'd surely only be executing the case
> statement's operator's oprrest function?  We'd need to ensure we don't
> invoke any casts that could error out.

Hmm, after a bit of thought I now see what you mean.  We'd need to
loop through each WHEN clause to ensure there's a Const and check if
that Const is equal to the Const on the other side of the OpExpr, then
select the first match. That, of course, must perform a comparison,
but, that's not really doing anything additional to what constant
folding code already does, is it?

David

David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, 13 Jun 2020 at 19:52, David Rowley <dgrowleyml@gmail.com> wrote:
>> On Sat, 13 Jun 2020 at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Hm.  Maybe we could reasonably assume that the equality operators used
>>> for such constructs are error-and-side-effect-free, thus dodging the
>>> semantic problem I mentioned in the other thread?

>> I'm only really talking about selectivity estimation only for now.
>> I'm not really sure why we'd need to ensure that the equality operator
>> is error and side effect free.  We'd surely only be executing the case
>> statement's operator's oprrest function?  We'd need to ensure we don't
>> invoke any casts that could error out.

> Hmm, after a bit of thought I now see what you mean.

No, you were right the first time: we're considering different things.
I was wondering about how to constant-fold a "CASE = constant" construct
as was being requested in the other thread.  Obviously, if that succeeds
then it'll simplify selectivity estimation too --- but it's reasonable
to also think about what to do for "CASE = constant" in selectivity
estimation, because with or without such a constant-folding rule,
there would be lots of cases that the rule fails to simplify.  Further
we should be thinking about how to get some estimate for cases that
the folding rule would fail at, so I'm not sure that we ought to restrict
our thoughts to constant comparisons.

In the cases I've seen so far, even a rule as dumb as "if the CASE has
N arms then estimate selectivity as 1/N" would be a lot better than
what we get now.

            regards, tom lane

I tested both postgres 12.3 and 13 beta 1 and the results are the same.

I could read the pg_ tables instead of the views in the information_schema
but that's the SQL standard schema so I'd prefer to stick to that.

I reported this issue because the performance gap is huge and that could be
useful to bring in some improvements.

The DDL is still evolving so a materialized table/view is not an option at
the moment.

I'll try to remove the <> 'CHECK' clause, I'm quite sure we needed that for
some reason but I didn't follow that change.

Thanks



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html