Re: BUG #2150: PL/Python function delcared STABLE gets run repeatedly

On Fri, Jan 06, 2006 at 02:48:59PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > Is caching of results for STABLE/IMMUTABLE functions called with a
> > constant something that would be reasonable to add?
>
> 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.)
>

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