Re: Stored Procedure Speed

[Richard Huxton <dev(at)archonet(dot)com>]

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