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.
regards, tom lane