Re: view reading information_schema is slow in PostgreSQL 12

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.

Pavel

> Regards
>
> Pavel
>
>
>>
>> --
>> Justin
>>
>>
>>