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.
