Re: view reading information_schema is slow in PostgreSQL 12

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?

Regards

Pavel

>
> --
> Justin
>
>
>