Обсуждение: SQL question

In the query:

    select field, length(field) from table where length(field) < 10;

How many times is the length() function called for each row?  Once or
twice?

Thanks,
--
Steve Chadsey <tyr@teiresias.net>

On Fri, 25 Jan 2002, Bolt Thrower wrote:

> In the query:
>
>     select field, length(field) from table where length(field) < 10;
>
> How many times is the length() function called for each row?  Once or
> twice?

Pretty sure it'll be twice.

On Fri, 25 Jan 2002, Bolt Thrower wrote:

> In the query:
>
>     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.

I did a similar query with an explain:

 explain verbose select behavior_descr, length(behavior_descr) from
behaviors where length(behavior_descr) < 20;
NOTICE:  QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 1.04 :rows 1 :width 22
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043
:restypmod 34 :resname behavior_descr :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2
:vartype 1043 :vartypmod 34  :varlevelsup 0 :varnoold 1 :varoattno 2}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname
length :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
EXPR :typeOid 23  :opType func :oper { FUNC :funcid 1319 :functype 23 }
                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
:args ({ VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34
:varlevelsup 0 :varnoold 1 :varoattno 2})}}) :qpqual ({ EXPR :typeOid 16
:opType op :oper { OPER :opno 97 :opid 66 :opresulttype 16 } :args ({ EXPR
:typeOid 23  :opType func :oper { FUNC :funcid 1319 :functype 23 } :args
                                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
({ VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 34  :varlevelsup 0
:varnoold 1 :varoattno 2})} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue  4 [ 20 0 0 0 ] })}) :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 }
NOTICE:  QUERY PLAN:

It looks like length() is getting run twice here.

-- Brett
                                          http://www.chapelperilous.net/
------------------------------------------------------------------------
And I alone am returned to wag the tail.

"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