Re: view reading information_schema is slow in PostgreSQL 12

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> On Sat, 13 Jun 2020 at 15:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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