Re: Stored Procedure Speed

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 ...  
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