Short-circuit boolean evaluation

No.

2011/4/30, Jon Smark <jon.smark@yahoo.com>:

Hi,

Does Postgresql perform short-circuit boolean evaluation both in SQL
and PL/pgSQL functions? As an example, suppose I have a function called
"do_stuff" which is computationally intensive. In the example below,
will it be called for rows for which the first predicate (foobar.id = $1)
is false?

SELECT count(*) FROM foobar WHERE foobar.id = $1 AND do_stuff (foobar.name);

Thanks!
Jon

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
------------
pasman

No. It will not be called
Or
No. Postgresql does not short-circuit boolean evaluations
?

On Apr 30, 2011, at 10:27, pasman pasmański <pasman.p@gmail.com> wrote:

On Sat, Apr 30, 2011 at 10:34:32AM -0400, David Johnston wrote:

No. It will not be called
Or
No. Postgresql does not short-circuit boolean evaluations
?

SQL is a somewhat declarative language. There is no "order" to
evaluation as such. So you can't talk about short circuiting either.
This applies to any SQL database.

You can somewhat enforce order with subselects and CASE and other such
constructs.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

David Johnston <polobo@yahoo.com> writes:

No. It will not be called
Or
No. Postgresql does not short-circuit boolean evaluations
?

The correct answer is "maybe". See
http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

regards, tom lane

On 2011-04-30, Jon Smark <jon.smark@yahoo.com> wrote:

Hi,

Does Postgresql perform short-circuit boolean evaluation both in SQL
and PL/pgSQL functions?

sometimes.

the planner will rearrange what you write,

for this reason it is very likely that

SELECT count(*) FROM foobar WHERE foobar.id = $1 AND do_stuff (foobar.name);

will perform as well as

SELECT count(*) FROM foobar WHERE do_stuff (foobar.name) and foobar.id = $1;

because the planner will rewrite this 'bad version' to execute the
same as the good version.

If you have an index on foobar.id or on do_stuff(foobar.name) or on
both it might be used to speed up the query.

You can give the planner a hint as to how expensive each function is
when you define the function.

in general the planner will take care of it for you

if you want to control when the function gets called with a boolean
test consider using case.

--
⚂⚃ 100% natural