Re: view reading information_schema is slow in PostgreSQL 12 - Mailing list pgsql-performance

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