From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: regrog <andrea.vencato@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: view reading information_schema is slow in PostgreSQL 12
Date: Fri, 12 Jun 2020 23:34:43 -0500
Message-ID: <20200613043443.GM14879@telsasoft.com> (raw)
In-Reply-To: <798686.1592017869@sss.pgh.pa.us>
References: <1591975278775-0.post@n3.nabble.com>
<798686.1592017869@sss.pgh.pa.us>
On Fri, Jun 12, 2020 at 11:11:09PM -0400, Tom Lane wrote:
> regrog <andrea.vencato@gmail.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').
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, tgl@sss.pgh.pa.us, andrea.vencato@gmail.com
Subject: Re: view reading information_schema is slow in PostgreSQL 12
In-Reply-To: <20200613043443.GM14879@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox