Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Stable function being evaluated more than once in a single query

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mark Liberman <mliberman(at)mixedsignals(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Stable function being evaluated more than once in a single query
Date: 2006-01-14 00:43:48
Message-ID: 20060114004348.GT9017@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-docspgsql-performance
Adding -docs...

On Fri, Jan 13, 2006 at 07:27:28PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)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(at)pervasive(dot)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(at)postgresql(dot)org so that your
       message can get through to the mailing list cleanly


In response to

Responses

pgsql-docs by date

Next:From: Anjan Kumar. A.Date: 2006-01-15 23:35:04
Subject: Problem with the Planner
Previous:From: Tom LaneDate: 2006-01-14 00:27:28
Subject: Re: Stable function being evaluated more than once in a single query

pgsql-performance by date

Next:From: Bruce MomjianDate: 2006-01-14 04:23:33
Subject: Re: Extremely irregular query performance
Previous:From: Tom LaneDate: 2006-01-14 00:27:28
Subject: Re: Stable function being evaluated more than once in a single query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group