Re: view reading information_schema is slow in PostgreSQL 12

so 13. 6. 2020 v 7:15 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> so 13. 6. 2020 v 7:13 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> napsal:
>
>>
>>
>> so 13. 6. 2020 v 6:34 odesílatel Justin Pryzby <pryzby(at)telsasoft(dot)com>
>> napsal:
>>
>>> On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
>>> > regrog <andrea(dot)vencato(at)gmail(dot)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='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
>>>
>>>
>>>