public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: Jim C. Nasby <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Mark Liberman <[email protected]>
Cc: [email protected]
Subject: Re: Stable function being evaluated more than once in a single
Date: Thu, 19 Jan 2006 17:52:14 -0500 (EST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
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" <[email protected]> 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 [email protected]
> 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 [email protected] so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
[email protected] | (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>
Attachments:
[text/plain] /bjm/diff (1.7K, 2-%2Fbjm%2Fdiff)
download | inline:
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>
view thread (6+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Stable function being evaluated more than once in a single
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox