Re: Stable function being evaluated more than once in a single - Mailing list pgsql-performance

From Bruce Momjian
Subject Re: Stable function being evaluated more than once in a single
Date
Msg-id 200601192252.k0JMqEj10766@candle.pha.pa.us
Whole thread Raw
In response to Re: Stable function being evaluated more than once in a single query  ("Jim C. Nasby" <jnasby@pervasive.com>)
List pgsql-performance
Here is updated documentation for STABLE.  I just changed a few words
for clarification.

---------------------------------------------------------------------------

Jim C. Nasby wrote:
> Adding -docs...
>
> On Fri, Jan 13, 2006 at 07:27:28PM -0500, Tom Lane wrote:
> > "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > > Is the issue that the optimizer won't combine two function calls (ie:
> > > SELECT foo(..) ... WHERE foo(..)), or is it that sometimes it won't make
> > > the optimization (maybe depending on the query plan, for example)?
> >
> > What the STABLE category actually does is give the planner permission to
> > use the function within an indexscan qualification, eg,
> >     WHERE indexed_column = f(42)
> > Since an indexscan involves evaluating the comparison expression just
> > once and using its value to search the index, this would be incorrect
> > if the expression's value might change from row to row.  (For VOLATILE
> > functions, we assume that the correct behavior is the naive SQL
> > semantics of actually computing the WHERE clause at each candidate row.)
> >
> > There is no function cache and no checking for duplicate expressions.
> > I think we do check for duplicate aggregate expressions, but not
> > anything else.
>
> In that case I'd say that the sSTABLE section of 32.6 should be changed
> to read:
>
> A STABLE function cannot modify the database and is guaranteed to
> return the same results given the same arguments for all calls within a
> single surrounding query. This category gives the planner permission to
> use the function within an indexscan qualification. (Since an indexscan
> involves evaluating the comparison expression just once and using its
> value to search the index, this would be incorrect if the expression's
> value might change from row to row.) There is no function cache and no
> checking for duplicate expressions.
>
> I can provide a patch to that effect if it's easier...
>
> On a related note, would it be difficult to recognize multiple calls of
> the same function in one query? ISTM that would be a win for all but the
> most trivial of functions...
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/xfunc.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v
retrieving revision 1.109
diff -c -c -r1.109 xfunc.sgml
*** doc/src/sgml/xfunc.sgml    29 Nov 2005 01:46:54 -0000    1.109
--- doc/src/sgml/xfunc.sgml    19 Jan 2006 22:43:58 -0000
***************
*** 899,911 ****
       <para>
        A <literal>STABLE</> function cannot modify the database and is
        guaranteed to return the same results given the same arguments
!       for all calls within a single surrounding query.  This category
!       allows the optimizer to optimize away multiple calls of the function
!       within a single query.  In particular, it is safe to use an expression
!       containing such a function in an index scan condition.  (Since an
!       index scan will evaluate the comparison value only once, not once at
!       each row, it is not valid to use a <literal>VOLATILE</> function in
!       an index scan condition.)
       </para>
      </listitem>
      <listitem>
--- 899,911 ----
       <para>
        A <literal>STABLE</> function cannot modify the database and is
        guaranteed to return the same results given the same arguments
!       for all rows within a single statement. This category allows the
!       optimizer to optimize multiple calls of the function to a single
!       call. In particular, it is safe to use an expression containing
!       such a function in an index scan condition. (Since an index scan
!       will evaluate the comparison value only once, not once at each
!       row, it is not valid to use a <literal>VOLATILE</> function in an
!       index scan condition.)
       </para>
      </listitem>
      <listitem>

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: query plans different for 8.1 on windows and aix
Next
From: Alex Turner
Date:
Subject: Re: 3WARE Card performance boost?