Thread: Short circuit evaluation of expressions in query
Say I have the following parameterized query in a function:
select * from foo where ($1 = -1 or foo.status = $1) and (…)
where the (…) part consists of more parameterized conditions similar to the first one.
Suppose that at runtime, $1 is supplied a value of -1, does the foo.status = $1 condition still have to be evaluated?
Assuming the $1 = -1 case, should the above query be as fast as the following?
select * from foo where (…)
Thanks
Jimmy
On Tue, Jun 13, 2006 at 02:34:36PM -0400, Jimmy Choi wrote: > Suppose that at runtime, $1 is supplied a value of -1, does the > foo.status = $1 condition still have to be evaluated? SQL doesn't offer any kind of guarentees about order of evaluations, and neither does postgres. It may occasionally try in an effort to reduce execution time, but there are no guarentees. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
"Jimmy Choi" <JCHOI@altera.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