FTA: “Why the query planner doesn’t automatically convert a condition like x > a OR (x == a AND y > b) to (x, y) > (a, b) is something I still don’t understand to this day, though.”
“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. For instance, if one wrote:
SELECT true OR somefunc();
then somefunc() would (probably) not be called at all. The same would be the case if one wrote:
SELECT somefunc() OR true;
Note that this is not the same as the left-to-right “short-circuiting” of Boolean operators that is found in some programming languages.”
There’s a difference between the two. https://www.postgresql.org/docs/current/sql-expressions.html...:
“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. For instance, if one wrote:
then somefunc() would (probably) not be called at all. The same would be the case if one wrote: Note that this is not the same as the left-to-right “short-circuiting” of Boolean operators that is found in some programming languages.”https://www.postgresql.org/docs/current/functions-comparison...:
“For the <, <=, > and >= cases, the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found.”
So, this is using something akin to short-circuiting.
I think that can mean the two give different results in the presence of null values.
⇒ I would guess the optimizer isn’t smart enough to detect when the second, stricter query will be equivalent and faster.