From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Mark Liberman <mliberman@mixedsignals.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Stable function being evaluated more than once in a single query
Date: Fri, 13 Jan 2006 19:27:28 -0500
Message-ID: <16007.1137198448@sss.pgh.pa.us> (raw)
In-Reply-To: <20060114000640.GN9017@pervasive.com>
References: <200601111641.20627.mliberman@mixedsignals.com>
<935.1137040403@sss.pgh.pa.us>
<20060114000640.GN9017@pervasive.com>
"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.
regards, tom lane
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: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jnasby@pervasive.com, mliberman@mixedsignals.com
Subject: Re: Stable function being evaluated more than once in a single query
In-Reply-To: <16007.1137198448@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox