IMMUTABLE?

From: David Wheeler <david(at)kineticode(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: IMMUTABLE?
Date: 2006-05-16 03:15:11
Message-ID: 2D4C0FA5-DB75-45EA-91D2-7B4F6DD55083@kineticode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Performance Folks,

I just had an article[1] published in which I demonstrated recursive
PL/pgSQL functions with this function:

CREATE OR REPLACE FUNCTION fib (
fib_for int
) RETURNS integer AS $$
BEGIN
IF fib_for < 2 THEN
RETURN fib_for;
END IF;
RETURN fib(fib_for - 2) + fib(fib_for - 1);
END;
$$ LANGUAGE plpgsql;

Naturally, it's slow:

try=# \timing
try=# select fib(28);
fib
--------
317811
(1 row)

Time: 10642.803 ms

Now, I mistakenly said in my article that PostgreSQL doesn't have
native memoization, and so demonstrated how to use a table for
caching to speed up the function. It's pretty fast:

try=# select fib_cached(28);
fib_cached
------------
317811
(1 row)

Time: 193.316 ms

But over the weekend, I was looking at the Pg docs and saw IMMUTABLE,
and said, "Oh yeah!". So I recreated the function with IMMUTABLE. But
the performance was not much better:

try=# select fib(28);
fib
--------
317811
(1 row)

Time: 8505.668 ms
try=# select fib_cached(28);
fib_cached
------------
317811
(1 row)

So, what gives? Am I missing something, or not understanding how
IMMUTABLE works?

Many TIA,

David

1. http://www.onlamp.com/pub/a/onlamp/2006/05/11/postgresql-plpgsql.html

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2006-05-16 03:21:51 Re: IMMUTABLE?
Previous Message Simon Riggs 2006-05-15 08:48:33 Re: Wrong plan for subSELECT with GROUP BY