Message-ID: <459C93BC.30502@modgraph-usa.com>
Date: Wed, 03 Jan 2007 21:42:20 -0800
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Thunderbird 1.5.0.9 (X11/20061206)
MIME-Version: 1.0
To: Adam Rich <adam.r@sbcglobal.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Trivial function query optimized badly
References: <00a801c72fb7$4ad7dd50$6400a8c0@dualcore>
In-Reply-To: <00a801c72fb7$4ad7dd50$6400a8c0@dualcore>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Adam Rich wrote:
> Craig,
> What version of postgres are you using?  I just tested this on PG 8.1.2
> and was unable to reproduce these results.  I wrote a simple function
> that returns the same text passed to it, after sleeping for 1 second.
> I use it in a where clause, like your example below, and regardless of
> the number of rows in the table, it still takes roughly 1 second,
> indicating to me the function is only called once.

Sorry, I forgot that critical piece of info: I'm using 8.1.4.

Your results would indicate that 8.1.2 creates a different plan than 8.1.4, or else there's some configuration parameter that's different between your installation and mine that causes a radically different plan to be used.  I assume you vacuum/analyzed the table before you ran the query.

> Is it possible that your function really isn't immutable? Would PG 
> realize this and fall back to treating it as VOLATILE ?

Now that you say this, this seems more like a bug with the definition of IMMUTABLE.  The function should only be called once if it's given a constant string, right?  So the fact that Postgres called it once per row is just wrong.

Craig