From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: regrog <andrea.vencato@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: view reading information_schema is slow in PostgreSQL 12
Date: Sat, 13 Jun 2020 00:07:33 -0400
Message-ID: <800752.1592021253@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvrnop0h+du73FZKEzxd0CGP3RUoWPNha2ekz_F0YMNaYA@mail.gmail.com>
References: <1591975278775-0.post@n3.nabble.com>
<798686.1592017869@sss.pgh.pa.us>
<CAApHDvrnop0h+du73FZKEzxd0CGP3RUoWPNha2ekz_F0YMNaYA@mail.gmail.com>
David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, 13 Jun 2020 at 15:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> This feels quite similar to [1].
Yeah, it's the same thing. As I commented in that thread, I'd seen
applications of the idea in information_schema views -- it's the
same principle of a view exposing a CASE construct that translates
a catalog column to what the SQL spec says should be returned, and
then the calling query trying to constrain that output.
> I wondered if it would be more simple to add some smarts to look a bit
> deeper into case statements for selectivity estimation purposes. An
> OpExpr like:
> CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN
> 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END = 'CHECK';
Hm. Maybe we could reasonably assume that the equality operators used
for such constructs are error-and-side-effect-free, thus dodging the
semantic problem I mentioned in the other thread?
regards, tom lane
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: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, andrea.vencato@gmail.com
Subject: Re: view reading information_schema is slow in PostgreSQL 12
In-Reply-To: <800752.1592021253@sss.pgh.pa.us>
* 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