Re: Possible improvement

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Paul van der Linden <paul(dot)doskabouter(at)gmail(dot)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
>