Re: Does psql evaluate OR conditions in order?

On Wednesday, January 16, 2013 at 08:40, Tom Lane wrote:

>HideMe (Robert James) writes:
>>Postgres 9.1
>>Ubuntu something.
>
>>We have a stored procedure that performs the following evaluations:
>
>>IF ST_Intersects(line1, line2) OR ST_Intersects(line3, line4) THEN
>>do stuff
>>ELSIF ST_Intersects(line5, line6) OR ST_Intersects(line7, line8) THEN
>>do stuffB
>>END IF;
>
>>98% of the time the 2nd half of these IF statements will never be true.
>>ST_Intersects is an expensive operations to perform.
>
>>Given we're executing this across a lot of data... avoiding
>>expensive calls is a good idea.
>>So, can we count on the evaluation of the 1st half of the IF's
>>being done first before trying the 2nd half,
>
>In principle the order of ORs is not guaranteed, but that mainly has to
>do with execution of query WHERE clauses, wherein the optimizer will
>feel free to move sub-clauses to different relations, join levels, etc.
>I think you're reasonably safe to assume that OR is left-to-right in
>this simple context.
>
>>or should we re-write the IF/ELSE to
>
>>IF ST_Intersects(line1, line2) THEN
>>do stuff
>>ELSIF ST_Intersects(line5, line6) THEN
>>do stuffB
>>ELSIF ST_Intersects(line3, line4) THEN
>>do stuff
>>ELSIF ST_Intersects(line7, line8) THEN
>>do stuffB
>>END IF;
>
>That doesn't look like it produces quite the same results ...
>
Agree. Unless the OP has prior (undeclared) knowledge that only
one of the four intersect conditions can ever be true and has
just put his majority cases first.

AFAICT, if this is the case, the proposed sequence would achieve
the optimisations wanted.

More generally...

In situations like this I keep having to pinch myself and repeat
"SQL is not C"
<http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL>
4.2.14. Expression Evaluation Rules
"The order of evaluation of subexpressions is not defined. In
particular, the inputs of an operator or function are not
necessarily evaluated left-to-right or in any other fixed order.

"Furthermore, if the result of an expression can be
determined by
evaluating only some parts of it, then other subexpressions might
not be evaluated at all. ..."

So evaluation order is not deterministic, and might (i.e., may
not) show "lazy evaluation".

So, as Tom says, it's "reasonably safe to assume", left to right
evaluation, but this is only for performance, since getting the
correct answer should not rely on this. (Think: really hard to
trace/detect intermittent errors.) And, the optimiser might
still plan to evaluate the second expression despite having a
result in hand.

So far fairly obvious (even if it does lack commitment :-)

May I extend/generalize the question to confirm the 9.1+
optimiser is taking the function cost parameter into account
when planning.

Specifically would the optimiser be much more likely (near
certain?) to plan for "lazy" evaluation if the function was
labelled as very expensive?

And, does it matter if "very expensive" is something like 100K
just to push the point?

Regards
Gavan Schneider