Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stored Procedure Speed

Stored Procedure Speed

From
"Scott Schulthess"
Date:

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

Re: Stored Procedure Speed

From
Richard Huxton
Date:
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

Re: Stored Procedure Speed

From
Tom Lane
Date:
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

Re: Stored Procedure Speed

From
Richard Huxton
Date:
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

Re: Stored Procedure Speed

From
Tom Lane
Date:
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