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 <tgl@xxxxxxxxxxxxx> wrote:
Paul van der Linden <paul.doskabouter@xxxxxxxxx> 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
