Re: Short circuit evaluation of expressions in query

"Jimmy Choi" <JCHOI(at)altera(dot)com> writes:
> Say I have the following parameterized query in a function:
> select * from foo where ($1 = -1 or foo.status = $1) and (...)

> Suppose that at runtime, $1 is supplied a value of -1, does the
> foo.status = $1 condition still have to be evaluated?

The PG manual points out that left-to-right short circuit is NOT part of
the language specification:
http://www.postgresql.org/docs/8.1/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

However, the real problem with thinking as you do above is that it's
micro-optimization on the wrong level. Usually the sort of optimization
you need to think about in SQL is whether the query allows an index to
be used to fetch the relevant rows. In the above, even if you have an
index on foo.status, it won't be used because the OR means that
potentially *every* row of foo matches the OR condition.

If you really have a need to sometimes fetch all the rows and sometimes
fetch only the ones with status = X, I'd suggest generating different
queries in those two cases.

regards, tom lane