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