Ok, as always there's a lot more to take into account then when just superficially looking at it. And indeed your counterexample shows that you'd have to include all the previous when-conditions too as false WHERE x=0 IS DISTINCT FROM true AND 1/x > 100, which could become quite messy (especially with nested cases....)
On Fri, Jun 5, 2020 at 9:02 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Paul van der Linden <paul.doskabou...@gmail.com> writes: > > For the case where it isn't known if the case expression itself is > indexed, > > technically that should be added as a decision-node in the query planner. > > That'd be fairly hard to do, if we're regarding this as an expression > simplification step, since expression simplification is run long before > any consideration is given to indexes. (Even if we were willing to > contemplate reversing that ordering, it'd be hard to do, because we > need the simplified expressions to compare to index expressions --- > else we'd get fooled by irrelevant discrepancies that simplification > is supposed to remove.) > > The alternative is to try to wire this into index path generation instead > of treating it as a general-purpose expression simplification ... but that > likewise seems pretty undesirable. If you've got a case like this, you'd > like it to be simplified whether it ends up as an indexqual or not. > > So, as I said, I'm inclined to dismiss David's complaint as an > impracticable requirement. The other issues I raised are far more > significant. > > BTW, speaking of correctness, this seems like a pretty dire > counterexample: > > SELECT ... FROM > (SELECT CASE WHEN x = 0 THEN 'zero' > WHEN 1/x > 100 THEN 'tiny' > ELSE 'whatever' END AS class, > ... > ) ss > WHERE ss.class = 'tiny'; > > Naive application of this transformation would convert the WHERE to > > WHERE 1/x > 100 > > creating divide-by-zero failures where there should be none. > I'm not sure how we get around that; in general the planner > has little clue which operations can throw what errors. > > regards, tom lane >
