[GENERAL] Stored Procedure Speed

[Scott Schulthess]

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