Re: view reading information_schema is slow in PostgreSQL 12

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].

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';

could be simplified to c.contype = 'c', which we should have
statistics for. There'd certainly be case statement forms that
couldn't be simplified, but I think this one could.

David

[1] https://www.postgresql.org/message-id/flat/CAApHDvr%2B6%3D7SZBAtesEavgOQ0ZC03syaRQk19E%2B%2BpiWLopTRbg%40mail.gmail.com#3ec465f343f1204446941df29fc9e715