Re: SQL question

"Brett W. McCoy" <bmccoy@chapelperilous.net> writes:
>> select field, length(field) from table where length(field) < 10;
>>
>> How many times is the length() function called for each row?  Once or
>> twice?

> I would expect it to run both times.

More specifically, the one in the select list will be evaluated at
every row where the WHERE clause succeeds.  So "twice per row" is
an overstatement, possibly a large overstatement.

Also, although PG is not smart about common subexpressions in the way
that Bolt is evidently hoping, it does know all about Boolean
short-circuiting.  If you have something like

select ... from table where foo = 42 and length(bar) < 10

then length(bar) will not get evaluated at rows where foo = 42 fails.

Note: this assumes that the planner does not choose to reorder the
WHERE clauses, which AFAIR it'd only do if it pulls out a WHERE clause
to use with an index.  For example, if there's an index on foo,
the clause foo = 42 will probably get pulled out and used for an
indexscan (thus, in effect, "evaluated first") no matter whether you
write it first or second.

An interesting point here is that in the presence of functional indexes,
"avoiding the function evaluation" is not necessarily what you want.
We could imagine having an index on length(bar), in which case
     where length(bar) < 10
reduces to a scan over the part of the index that has entries less than
10.  I'm not sure this would be a win for length(), but for expensive
functions that have a wider variation in probable values than length()
does, it can be a huge win.

            regards, tom lane