Re: IMMUTABLE?

From: Joachim Wieland <joe(at)mcknight(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: David Wheeler <david(at)kineticode(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: IMMUTABLE?
Date: 2006-05-16 09:48:24
Message-ID: 20060516094824.GA2376@mcknight.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Qingqing Zhou 2006-05-16 10:01:26 Re: Pgsql (and mysql) benchmark on T2000/Solaris and some profiling
Previous Message Arjen van der Meijden 2006-05-16 09:33:32 Pgsql (and mysql) benchmark on T2000/Solaris and some profiling