Thread: Short-circuit boolean evaluation

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

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.

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

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

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/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
>                                       - Charles de Gaulle

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