From: | "Scott Schulthess" <scott(at)topozone(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Stored Procedure Speed |
Date: | 2007-04-25 14:15:45 |
Message-ID: | 4BF377919225F449BB097CB76FFE9BC80198802A@ptolemy.topozone.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hey Ya'll,
I'm a little puzzled by the speed of the stored procedures I am writing.
Here is the query alone in pgAdmin
select distinct featuretype from gnis_placenames where state='CT'
TIME: 312+16ms
Here is a stored procedure
create or replace function getfeaturetypes(text) returns setof text as
$$
select distinct featuretype from gnis_placenames where state=$1;
$$ language sql;
TIME: 2391+15ms
Now if I hardcode the stored procedure with the input
create or replace function getfeaturetypes(text) returns setof text as
$$
select distinct featuretype from gnis_placenames where state='CT';
$$ language sql;
TIME: 312+16ms
I also tried plPgsql
CREATE OR REPLACE FUNCTION getfeaturetypes(text) returns setof text as
$$
declare r record;
begin
for r in SELECT featuretype as text from gnis_placenames where state=$1
group by featuretype order by featuretype asc
LOOP
return next r.text;
END LOOP;
return;
end;
$$ language plpgsql;
grant execute on function getfeaturetypes(text) to tzuser;
TIME: 2609+16ms
What gives? How can I speed up this stored procedure?
-Scott
From | Date | Subject | |
---|---|---|---|
Next Message | Tilmann Singer | 2007-04-25 14:22:24 | Re: Audit-trail engine: getting the application's layer user_id |
Previous Message | Marcelo de Moraes Serpa | 2007-04-25 14:15:38 | Re: Audit-trail engine: getting the application's layer user_id |