public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jim C. Nasby <[email protected]>
To: Tom Lane <[email protected]>
Cc: Mark Liberman <[email protected]>
Cc: [email protected]
Subject: Re: [PERFORM] Stable function being evaluated more than once in a single query
Date: Fri, 13 Jan 2006 18:43:48 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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
view thread (6+ messages) latest in thread
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]
Subject: Re: [PERFORM] Stable function being evaluated more than once in a single query
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