Date: Tue, 16 May 2006 11:48:24 +0200
From: Joachim Wieland <joe@mcknight.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Wheeler <david@kineticode.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: IMMUTABLE?
Message-ID: <20060516094824.GA2376@mcknight.de>
References: <2D4C0FA5-DB75-45EA-91D2-7B4F6DD55083@kineticode.com>
	<5211.1147749711@sss.pgh.pa.us>
	<3F0CF576-8C6F-433C-9942-BB36F4E1A194@kineticode.com>
	<5894.1147753901@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <5894.1147753901@sss.pgh.pa.us>
User-Agent: Mutt/1.5.9i

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