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