Re: view reading information_schema is slow in PostgreSQL 12

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

so 13. 6. 2020 v 7:15 odesílatel Pavel Stehule <pavel.stehule@xxxxxxxxx> napsal:

so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule <pavel.stehule@xxxxxxxxx> napsal:

so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby <pryzby@xxxxxxxxxxxxx> napsal:

On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
> regrog <andrea.vencato@xxxxxxxxx> 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