The following bug has been logged online:

Bug reference: 2150
Logged by: Aaron Swartz
Email address: me (AT) aaronsw (DOT) com
PostgreSQL version: 8.1.1
Operating system: Mac OS X
Description: PL/Python function delcared STABLE gets run repeatedly
Details:

We have the PL/Python function get_foo(n) that returns an array. If this
function is declared IMMUTABLE, then:

SELECT * FROM tablenamehere WHERE id = any(get_foo(21));

returns quickly. If the function is declared STABLE, however, PostgreSQL
seems to incorrectly run it for every row in the table and it takes forever.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Aaron Swartz" <me (AT) aaronsw (DOT) com> writes:
= ANY isn't indexable in 8.1, so there's no obvious reason why either
version would be fast. You're going to have to provide more detail if
you want help. For starters, what does EXPLAIN ANALYZE show in each
case? What's the schema of the table (\d output will do)?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Aaron Swartz <me (AT) aaronsw (DOT) com> writes:
Oh, you are misunderstanding the point of IMMUTABLE/STABLE.
STABLE essentially gives the planner permission to use the function
in an indexscan qualification. It does *not* cause any caching of
the function result in other contexts, which is what you seem to be
wishing would happen.

IMMUTABLE/STABLE/VOLATILE are promises from you to the system about
the behavior of the function, not promises from the system about
how it will choose to evaluate the function.

What I'd suggest is recasting the function to return a SETOF result
instead of an array, and then writing

SELECT * FROM tablename WHERE id IN (select * from foo(21))

This should get you a plan that will work reasonably well for you.

regards, tom lane

---------------------------(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

On Fri, Jan 06, 2006 at 12:43:06PM -0500, Tom Lane wrote:
Is caching of results for STABLE/IMMUTABLE functions called with a
constant something that would be reasonable to add? I certainly wish
this happened natively, but I've always hacked around it by

SELECT ... WHERE x = (SELECT foo(21))

so presumably some kind of query transform logic similar to what was
done for min/max might work.

I don't see anything about this on the TODO...
--
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 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Jim C. Nasby" <jnasby (AT) pervasive (DOT) com> writes:
I tend to think not: the distributed overhead would outweigh the
advantages. See previous discussions in the archives.

(However, the previous discussions have imagined an explicit cache
that stores function names, arguments, and results. Your thought
of transforming the call into an InitPlan node might be interesting.
The trick is still to know which functions are expensive enough to
justify the overhead of an InitPlan.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, Jan 06, 2006 at 02:48:59PM -0500, Tom Lane wrote:
Oh, yeah, actually caching results between queries doesn't make sense; I
think that's pretty much a corner-case that people should be expected to
code up/handle themselves. I'm just looking for only executing the
function once per query.

Yeah, some heuristics to try and tweak between adding a seperate
planning node and just running the function a few times would be nice,
but I suspect that's overkill. I think it would be fine to just assume
that any time you're going to execute a function more than X number of
times (where X should be easy to deduce during query planning), you'll
be better off taking the cost to tweak the plan to only run it once.

How does the overhead of an InitPlan compare to the overhead of calling
a SQL function? Or plpgsql? Or one of the external function languages?
--
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