Hello, pgsql-hackers.

I think It would be useful to cache return values for stable
and immutable functions. Now thay are really called everytime.

Demonstration (Postgresql 8.0.1 SlackWare10.0):

create table t
(
i int PRIMARY KEY
) without oids;

insert into t values (1);
insert into t values (2);
insert into t values (3);
insert into t values (4);
insert into t values (5);
insert into t values (6);
insert into t values (7);
insert into t values (8);
insert into t values (9);
insert into t values (10);

create table tt
(
x int,
y int,
PRIMARY KEY (x,y)
) without oids;

insert into tt (x,y) select t1.i,t2.i from t t1,t t2;

create function more_with_count(int,int) returns boolean as $BODY$
$_SHARED{count}++;
return (@_[0]>@_[1])?'t':'f';
$BODY$
language plperl stable;

create function get_count() returns int as $BODY$
my $c=$_SHARED{count};
$_SHARED{count}=0;
return $c;
$BODY$
language plperl;

select * from tt ,t where more_with_count(tt.x,t.i);

select get_count();
/*
returns:
get_count()
-----------
1000
*/

Don't you think that get_count=100 is enough?

Motivation (simplified):

I have a hierarchical table for groups:

create table group
(
id int PRIMARY KEY;
pid int,
descr varchar(100),
CONSTRAINT CHECK (pid IS NULL or pid<id)
);
--If pid is NULL then group is a root for some tree in a forest

--A table for group price:

create table direction
(
direction varchar(20),
group_id int,
price numeric(6,4) NOT NULL,
PRIMARY KEY (direction,group_id)
);

create index ix_direction on direction ( (direction || chr(255)), direction, group_id )
--and (part of) table with idetified phonecalls:

create table calls
(
id_call int PRIMARY KEY,
called_nom varchar(20) NOT NULL,
group_id int
);

/*
I wish to write function like this (it may contains errors,
couse after some test on stable functions I realized it would be slow and not test)
*/
create function is_parent_or_self(int,int) returns boolean as $BODY$
declare
this int;
descendant int;
begin
this:=$1;
descendant:=$2;
while not descendant is NULL and this<>descendant loop
select into descendant id from group where pid=descendant;
end loop;
if this=descendant then
return true;
end if;
return false;
end;
$BODY$
language plpgsql stable;

-- And set price for a call with a query
select distinct on (id_call) id_call,price
from
calls c inner join
direction d on
c.called_nom||chr(255)>d.direction
and c.called_nom<=d.direction
inner join
group g
on d.group_id=g.id and is_parent_or_self(c.group_id,g.id)
order by id_call,d.direction desc,g.id desc;

/* there are not more than 15 active groups from 30 total
and I think that 400 hash keys without list, so that
each hashkey equality for a different argument list would
replace previous cached value, would enough for
speed up query.
*/

Syntactically it may be formed as:

set enable_stable_function_cache=on; -- And somewhat in postgresql.conf
set max_stable_function_cache=1000;


create function ...
stable cached(400);

And I think it useful to cache values for immutable functioins
accros whole connection.
--
falcon mailto:falcon@intercable.ru



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

falcon <falcon@intercable.ru> writes:
> I think It would be useful to cache return values for stable
> and immutable functions. Now thay are really called everytime.

In most circumstances the overhead of searching such a cache would swamp
any benefit we might get from it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Fri, Apr 15, 2005 at 12:54:24PM -0400, Tom Lane wrote:
> falcon <falcon@intercable.ru> writes:
> > I think It would be useful to cache return values for stable
> > and immutable functions. Now thay are really called everytime.
>
> In most circumstances the overhead of searching such a cache would swamp
> any benefit we might get from it.

Maybe falcon would benefit from using pgmemcache in the first place ...

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"No es bueno caminar con un hombre muerto"

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq