Paul van der Linden <paul.doskabouter@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.
