function taking a long time. any options o speed it up.

Rhys Stewart · *nix forums beginner Joined: 12 May 2006 Posts: 6

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$
;

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr · *nix forums Guru Joined: 02 Mar 2005 Posts: 674

On Tue, Jul 18, 2006 at 08:11:40AM -0500, Rhys Stewart wrote:

Rhys Stewart · *nix forums beginner Joined: 12 May 2006 Posts: 6

well i started by creating a table with the data i needed, instead of
working on the big table....that helped a whole lot.

thanks.
On 7/18/06, Michael Fuhr <mike@fuhr.org> wrote:

Google