Thread: Performance of plpgsql functions

The following represents a simplified version of my database setup:

=============== BEGIN ================
create table A(
id int not null,
scale smallint,
val smallint[]
);
create view V as select id,q15(val,scale) as data from V;

create or replace function q15(vals smallint[], exponent smallint)
returns real[]
as '
declare
   low int;
   high int;
   count int;
   new_array real[];
   multiplier real;
begin
   if vals is null or exponent is null
   then
      return null;
   end if;

   multiplier := 2^(exponent-15);

   low := array_lower(vals,1);
   high := array_upper(vals,1);

   for i in low..high loop
      new_array[i] := vals[i] * multiplier;
   end loop;

   return new_array;
end;
' language plpgsql immutable returns null on null input;

================ END ===============

I am running Postgres 8.1.2 on dual AMD x86_64 with 2G of shared memory.

Each time I issue:  select data[10] from V where id>=5000 and id<=10000;
my database server takes up 100% of one of my two processors to compute the results. I was hoping that the "immutable" keyword would tell the database server to cache the results as much as possible, but I don't see its physical manifestation as yet. I am wondering if I was doing something in my function that is the main cause of the CPU utilization.

Expanding the data is not a good option, since it will increase my database foot-print substantially since I have multiple fields which are stored the same way as "V.data" above. I've thought about creating a C-function to replace my plpgsql function, but don't know how useful would that be.

Thanks.