Stored Procedure Speed
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
Scott Schulthess wrote:
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+16msHere 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
Basically, the planner has more information with the hard-coded example.
It should know enough to come up with different plans for 'CT' and XX'.
Functions (and this varies per-language, but plpgsql is the usual
culprit) cache their query-plans, so you end up with "one size fits all".
You can see what plan it comes up with by using PREPARE ... <query>
followed by EXPLAIN EXECUTE ...
I must say I thought recent versions of PG delayed planning the query
until first call though.
--
Richard Huxton
Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
I must say I thought recent versions of PG delayed planning the query
until first call though.
No, you're thinking of parameterized queries sent through the FE/BE
protocol. Functions still plan without any assumptions about parameter
values.
regards, tom lane
Tom Lane wrote:
Richard Huxton <dev@archonet.com> writes:
I must say I thought recent versions of PG delayed planning the query
until first call though.No, you're thinking of parameterized queries sent through the FE/BE
protocol. Functions still plan without any assumptions about parameter
values.
Ah, fair enough. I *am* right in thinking that trivial SQL functions
will have their expressions inlined though?
--
Richard Huxton
Archonet Ltd