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
