From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: regrog <andrea.vencato@gmail.com>,
        pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: view reading information_schema is slow in PostgreSQL 12
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>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Sat, 13 Jun 2020 15:55:46 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <800751.1592021253.1@sss.pgh.pa.us>
Date: Sat, 13 Jun 2020 00:07:33 -0400
Message-ID: <800752.1592021253@sss.pgh.pa.us>
Precedence: bulk

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