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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
