Обсуждение: 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+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 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
Richard Huxton <dev@archonet.com> writes: > Ah, fair enough. I *am* right in thinking that trivial SQL functions > will have their expressions inlined though? Yes. regards, tom lane