Search Postgresql Archives

Stored Procedure Speed

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux