David Rowley <dgrowleyml@xxxxxxxxx> writes: > On Sat, 13 Jun 2020 at 15:11, Tom Lane <tgl@xxxxxxxxxxxxx> 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
