Stored Procedure Speed

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

Responses

Browse pgsql-general by date

  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