Re: Short-circuit boolean evaluation

On 2011-04-30, Jon Smark <jon(dot)smark(at)yahoo(dot)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