view reading information_schema is slow in PostgreSQL 12

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

view reading information_schema is slow in PostgreSQL 12

regrog
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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Imre Samu
> 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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Tom Lane-2
In reply to this post by regrog
regrog <[hidden email]> 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.952 rows=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=1839 loops=1)
                                          ...
                        ->  Subquery Scan on "*SELECT* 2"  (cost=1.44..901.27 rows=315 width=192) (actual time=0.001..0.001 rows=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=1848 loops=1)
                      Filter: ((contype <> ALL ('{t,x}'::"char"[])) 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::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::text END)::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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

David Rowley
In reply to this post by regrog
On Sat, 13 Jun 2020 at 06:26, regrog <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

David Rowley
In reply to this post by Tom Lane-2
On Sat, 13 Jun 2020 at 15:11, Tom Lane <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Tom Lane-2
David Rowley <[hidden email]> writes:
> On Sat, 13 Jun 2020 at 15:11, Tom Lane <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Justin Pryzby
In reply to this post by Tom Lane-2
On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
> regrog <[hidden email]> 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='FOREIGN KEY';

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

--
Justin


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Pavel Stehule


so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby <[hidden email]> napsal:
On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
> regrog <[hidden email]> 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='FOREIGN KEY';

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

Tomas Vondra is working on functional statistics. Can it be the solution of CASE issue?

Regards

Pavel
 

--
Justin


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Pavel Stehule


so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule <[hidden email]> napsal:


so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby <[hidden email]> napsal:
On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
> regrog <[hidden email]> 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='FOREIGN KEY';

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

Tomas Vondra is working on functional statistics. Can it be the solution of CASE issue?

and maybe workaround.  Can we use functional index there. It has a statistics.

Pavel


Regards

Pavel
 

--
Justin


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Pavel Stehule


so 13. 6. 2020 v 7:15 odesílatel Pavel Stehule <[hidden email]> napsal:


so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule <[hidden email]> napsal:


so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby <[hidden email]> napsal:
On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
> regrog <[hidden email]> 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='FOREIGN KEY';

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

Tomas Vondra is working on functional statistics. Can it be the solution of CASE issue?

and maybe workaround.  Can we use functional index there. It has a statistics.

create table foo(a int);
insert into foo select random()* 3 from generate_series(1,1000000);
create view x as select case when a = 0 then 'Ahoj' when a = 1 then 'nazdar' when a = 2 then 'Hi' end from foo;
analyze foo;

postgres=# explain analyze select * from x where "case" = 'Ahoj';
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                       QUERY PLAN                                                                       │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Gather  (cost=1000.00..14273.96 rows=5000 width=32) (actual time=1.265..129.771 rows=166744 loops=1)                                                   │
│   Workers Planned: 2                                                                                                                                   │
│   Workers Launched: 2                                                                                                                                  │
│   ->  Parallel Seq Scan on foo  (cost=0.00..12773.96 rows=2083 width=32) (actual time=0.031..63.663 rows=55581 loops=3)                                │
│         Filter: (CASE WHEN (a = 0) THEN 'Ahoj'::text WHEN (a = 1) THEN 'nazdar'::text WHEN (a = 2) THEN 'Hi'::text ELSE NULL::text END = 'Ahoj'::text) │
│         Rows Removed by Filter: 277752                                                                                                                 │
│ Planning Time: 0.286 ms                                                                                                                                │
│ Execution Time: 137.538 ms                                                                                                                             │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)

create index on foo((CASE WHEN (a = 0) THEN 'Ahoj'::text WHEN (a = 1) THEN 'nazdar'::text WHEN (a = 2) THEN 'Hi'::text ELSE NULL::text END));
analyze foo;

postgres=# explain analyze select * from x where "case" = 'Ahoj';
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│                                                                         QUERY PLAN                                                                      
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
│ Bitmap Heap Scan on foo  (cost=1862.67..10880.17 rows=167000 width=32) (actual time=16.992..65.300 rows=166744 loops=1)                                
│   Recheck Cond: (CASE WHEN (a = 0) THEN 'Ahoj'::text WHEN (a = 1) THEN 'nazdar'::text WHEN (a = 2) THEN 'Hi'::text ELSE NULL::text END = 'Ahoj'::text)  
│   Heap Blocks: exact=4425                                                                                                                              
│   ->  Bitmap Index Scan on foo_case_idx  (cost=0.00..1820.92 rows=167000 width=0) (actual time=16.293..16.293 rows=166744 loops=1)                      
│         Index Cond: (CASE WHEN (a = 0) THEN 'Ahoj'::text WHEN (a = 1) THEN 'nazdar'::text WHEN (a = 2) THEN 'Hi'::text ELSE NULL::text END = 'Ahoj'::tex
│ Planning Time: 0.768 ms                                                                                                                                
│ Execution Time: 72.098 ms                                                                                                                              
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
(7 rows)

Regards

Pavel

 

Pavel


Regards

Pavel
 

--
Justin


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

David Rowley
In reply to this post by Tom Lane-2
On Sat, 13 Jun 2020 at 16:07, Tom Lane <[hidden email]> wrote:

>
> David Rowley <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

David Rowley
On Sat, 13 Jun 2020 at 19:52, David Rowley <[hidden email]> wrote:

>
> On Sat, 13 Jun 2020 at 16:07, Tom Lane <[hidden email]> wrote:
> >
> > David Rowley <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Tom Lane-2
David Rowley <[hidden email]> writes:
> On Sat, 13 Jun 2020 at 19:52, David Rowley <[hidden email]> wrote:
>> On Sat, 13 Jun 2020 at 16:07, Tom Lane <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

Michael Lewis
In reply to this post by regrog
On Fri, Jun 12, 2020 at 12:26 PM regrog <[hidden email]> 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.

I have a view that abstracts the data in the database:

CREATE OR REPLACE VIEW public.my_constraints


Assuming your DDL changes fairly seldomly, and you already have a well structured deployment process in place for that, perhaps just change this to a materialized view and refresh (concurrently) after any DDL gets executed. That way, you have stats on what your view has in it and are not subject to issues with planning the execution of the query in this view. 
Reply | Threaded
Open this post in threaded view
|

Re: view reading information_schema is slow in PostgreSQL 12

regrog
In reply to this post by regrog
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