function taking a long time. any options o speed it up. - Mailing list pgsql-general

Hi all,

i created a function and it takes a long time to run. I was testing it
as a wrote it and to the first drop table it takes just short of a
second. however when the rest of the code is added on, it takes
upwards of 5 minutes. Not the most appropriate thing. Are there any
tips out there for making functions go faster?

the code follows:

CREATE OR REPLACE FUNCTION "public".interp_point(character varying)
RETURNS varchar
LANGUAGE plpgsql
VOLATILE
AS
$not_in_route$

DECLARE
rout ALIAS FOR $1;
onlygeom geometry;
mathai record;
minthresh integer;
maxthresh integer;
tempname varchar;
amount integer;
total integer;
recset record;
route_len float8;
route_time integer;
prem_time integer;
cur_perc numeric(5,2) default 50;
perc_old numeric(5,2) default 50;
dif  numeric(5,2) default 0;
BEGIN
    tempname := 'tmp_'||$1;
    EXECUTE 'create table ' || tempname || ' as select
route,centroid(geomunion(the_geom)) from route where route = ' ||
quote_literal(rout) || ' group by route';
    EXECUTE 'SELECT distance(the_geom,(select centroid from '||
tempname ||' ))::int as dist from route where route = '||
quote_literal(rout) ||'order by dist limit 1' into minthresh;
    EXECUTE 'SELECT distance(the_geom,(select centroid from '||
tempname ||' ))::int as dist from route where route = '||
quote_literal(rout) ||'order by dist desc limit 1' into maxthresh;
    EXECUTE 'SELECT count(prem) from route where route = ' ||
quote_literal(rout) || '  AND the_geom && expand((select centroid from
' || tempname ||'),'|| minthresh||')' into amount;
    SELECT INTO total count(prem) from route where route = rout;
    SELECT INTO cur_perc ((amount::float/total::float)*100)::numeric(5,2);
    LOOP
          minthresh := minthresh + 90;
          perc_old :=  cur_perc;
          EXECUTE 'SELECT count(prem) from route where route = '
            || quote_literal(rout)
            || '  AND the_geom && expand((select centroid from '
            || tempname ||'),'|| minthresh||')' into amount;
          select into cur_perc ((amount::float/total::float)*100)::numeric(5,2);
          dif := abs(cur_perc - perc_old);
          IF dif < 3.25 AND cur_perc > 40 THEN
              EXIT;
          END IF;
    END LOOP;

    EXECUTE 'UPDATE ROUTE SET the_geom = null, matchtype = ' ||
quote_literal('4GEOCODE') || ' where route = '
    || quote_literal(rout) || ' AND the_geom is null OR (matchtype ~* '
|| quote_literal('route') || 'OR matchtype ~* '||
quote_literal('t[e]*mp') || 'OR matchtype ~* '||
quote_literal('place')
    || 'OR matchtype ~* '|| quote_literal('fuzzy') || 'OR matchtype ~*
'|| quote_literal('cent')
    ||') OR prem NOT in (select prem from route where route = '   ||
quote_literal(rout) || '  and  the_geom && expand((select centroid
from ' || tempname ||'),'|| minthresh||'))';
    EXECUTE 'DROP TABLE '|| tempname;
    EXECUTE 'create table ' || tempname || ' as select
makeline(the_geom) from (SELECT the_geom from route where route = '
||quote_literal(rout)|| 'order by timestamp) as the_geom ';
    EXECUTE 'SELECT length(makeline) FROM ' ||tempname INTO route_len;
    EXECUTE 'SELECT time2sec((select timestamp from route where route
= '||quote_literal(rout)||' order by timestamp desc limit 1) - (select
timestamp from route where route = '
    ||quote_literal(rout) || 'order by timestamp  limit 1))' INTO route_time;
    FOR  mathai IN EXECUTE 'SELECT * FROM route WHERE route = ' ||
quote_literal(rout) || ' AND matchtype = '||quote_literal('4GEOCODE')
||' order by timestamp' LOOP
    EXECUTE 'SELECT time2sec((select timestamp from route where route =
'||quote_literal(rout)||' order by timestamp desc limit 1) - (select
timestamp from route where prem = '
    ||quote_literal(mathai.prem)||'))' INTO prem_time;
    perc_old:= (route_time - prem_time)/route_time;
    EXECUTE 'SELECT line_interpolate_point((SELECT makeline from '||
tempname ||') ,' ||perc_old||')' INTO onlygeom;
    EXECUTE 'UPDATE route SET the_geom = '|| quote_literal(onlygeom) ||
'WHERE prem = ' || quote_literal(mathai.prem);
    END LOOP;
EXECUTE 'DROP TABLE '|| tempname;
select into recset route_len, amount,total,cur_perc,minthresh,maxthresh,dif;
    RETURN recset;

END;

$not_in_route$
;