Re: IMMUTABLE? - Mailing list pgsql-performance

From Joachim Wieland
Subject Re: IMMUTABLE?
Date
Msg-id 20060516094824.GA2376@mcknight.de
Whole thread Raw
In response to Re: IMMUTABLE?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: IMMUTABLE?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
On Tue, May 16, 2006 at 12:31:41AM -0400, Tom Lane wrote:
> It's true that the system *could* memoize (or in our more usual
> parlance, cache function values) given the assumptions embodied in
> IMMUTABLE.  But we don't, and I don't see any statement in the docs
> that promises that we do.  For 99% of the functions that the planner
> deals with, memoization would be seriously counterproductive because
> the function evaluation cost is comparable to if not less than the
> lookup cost in a memo table.  (int4pl is a good case in point.)

This seems to change as soon as one takes into account user functions.

While most immutable functions really seem to be small and their execution
fast, stable functions often hide complex sql (sometimes combined with
if-then-else or other program flow logic).

So irrespective of caching to prevent evaluation across statements, within a
single statement, is there a strong reason why for example in
WHERE col = f(const) with f() declared as immutable or stable and without an
index on col, f() still gets called for every row? Or is this optimization
just not done yet?


Joachim


pgsql-performance by date:

Previous
From: Arjen van der Meijden
Date:
Subject: Pgsql (and mysql) benchmark on T2000/Solaris and some profiling
Next
From: "Qingqing Zhou"
Date:
Subject: Re: Pgsql (and mysql) benchmark on T2000/Solaris and some profiling