From: Tom Lane <tgl@sss.pgh.pa.us>
To: regrog <andrea.vencato@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: view reading information_schema is slow in PostgreSQL 12
In-reply-to: <1591975278775-0.post@n3.nabble.com>
References: <1591975278775-0.post@n3.nabble.com>
Comments: In-reply-to regrog <andrea.vencato@gmail.com>
	message dated "Fri, 12 Jun 2020 08:21:18 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <798685.1592017869.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 12 Jun 2020 23:11:09 -0400
Message-ID: <798686.1592017869@sss.pgh.pa.us>
Precedence: bulk

regrog <andrea.vencato@gmail.com> writes:
> I'm facing performance issues migrating from postgres 10 to 12 (also fro=
m 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 =3D '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=3D0.29..1127.54 rows=3D316 width=3D192=
) (actual time=3D0.068..11.116 rows=3D1839 loops=3D1)
                        ->  Subquery Scan on "*SELECT* 1"  (cost=3D0.29..2=
26.26 rows=3D1 width=3D192) (actual time=3D0.068..10.952 rows=3D1839 loops=
=3D1)
                              ->  Result  (cost=3D0.29..226.25 rows=3D1 wi=
dth=3D288) (actual time=3D0.067..10.707 rows=3D1839 loops=3D1)
                                    One-Time Filter: (((current_database()=
)::information_schema.sql_identifier)::text =3D 'testzeal'::text)
                                    ->  Nested Loop  (cost=3D0.29..226.25 =
rows=3D1 width=3D288) (actual time=3D0.055..10.454 rows=3D1839 loops=3D1)
                                          ...
                        ->  Subquery Scan on "*SELECT* 2"  (cost=3D1.44..9=
01.27 rows=3D315 width=3D192) (actual time=3D0.001..0.001 rows=3D0 loops=3D=
1)
                              ->  Result  (cost=3D1.44..898.12 rows=3D315 =
width=3D288) (actual time=3D0.001..0.001 rows=3D0 loops=3D1)
                                    One-Time Filter: (((('CHECK'::characte=
r varying)::information_schema.character_data)::text <> 'CHECK'::text) AND=
 (((current_database())::information_schema.sql_identifier)::text =3D 'tes=
tzeal'::text) AND ((('CHECK' (...)
                                    ->  Nested Loop  (cost=3D1.44..898.12 =
rows=3D315 width=3D288) (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=3D0.29..199.30 rows=3D1 wid=
th=3D352) (actual time=3D0.382..45.343 rows=3D1848 loops=3D1)
            ->  Result  (cost=3D0.29..199.29 rows=3D1 width=3D512) (actual=
 time=3D0.381..44.384 rows=3D1848 loops=3D1)
                  One-Time Filter: (((current_database())::information_sch=
ema.sql_identifier)::text =3D 'testzeal'::text)
                  ->  Nested Loop  (cost=3D0.29..199.28 rows=3D1 width=3D2=
57) (actual time=3D0.376..40.953 rows=3D1848 loops=3D1)
                        ...

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=3D0.00..192.60 ro=
ws=3D14 width=3D73) (actual time=3D0.340..3.962 rows=3D1848 loops=3D1)
                      Filter: ((contype <> ALL ('{t,x}'::"char"[])) AND ((=
CASE contype WHEN 'c'::"char" THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FO=
REIGN KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char=
" THEN 'UNIQUE'::text ELSE NULL::text END)::text <> 'CHECK'::text) AND ((C=
ASE contype WHEN 'c'::"char" THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FOR=
EIGN KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char"=
 THEN 'UNIQUE'::text ELSE NULL::text END)::text =3D '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