optimiser STABLE vs. temp table was: HOWTO caching data across function calls: temporary tables, cursor?

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: optimiser STABLE vs. temp table was: HOWTO caching data across function calls: temporary tables, cursor?
Date: 2008-04-01 14:54:12
Message-ID: 20080401165412.3bfe172d@webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tue, 1 Apr 2008 16:08:45 +0200
"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

[snip]

> Since you are looking for a sample, maybe something like that can
> get you started:
>
> CREATE SEQUENCE temp_names;

[snip]

I was thinking something in the line of it.
I wasn't that sure of the course to follow.
Just few hours ago it looked like a swamp. Thanks, now I've more
confidence it is a feasible plan.

> > For me it's not clear if adding some additional caching system
> > (eg. create a temp table inside the function) would obtain the
> > same result as marking the function STABLE.

> No, STABLE doen't help here - that is only a hint for the optimizer.

I can't really appreciate the difference... or better... I think the
difference may be that I can't take for granted the function will be
cached if I delegate the choice to the optimiser.

I'd say the optimiser will try to cache it unless it finds something
better to do with the memory.
If that's what happens I think I'll avoid to complicate my life with
a manual cache management and just wrap the most general query in a
function.

Can anybody confirm that's how the optimiser work or explain the
differences between providing a "manual" cache and just declaring a
function STABLE?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-04-01 15:00:15 Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong
Previous Message Simon Riggs 2008-04-01 14:46:15 Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong