Skip site navigation (1) Skip section navigation (2)

Re: Performance problems inside a stored procedure.

From: Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk>
To: Euler Taveira de Oliveira <euler(at)timbira(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Subject: Re: Performance problems inside a stored procedure.
Date: 2008-01-29 17:38:13
Message-ID: 479F6485.9@rwa-net.co.uk (view raw or flat)
Thread:
Lists: pgsql-performance
Thanks Euler,

I made the change to STABLE but it didn't seem to make any difference.  
On closer inspection it seems to have been a casting problem, I was 
passing a varchar into the function and then testing this for equality 
with an integer.  The planner seems to have been unable to use this to 
access the index and so was returning too many rows and then filtering 
them.  It looks like I still have to take a hit of 2ms or so to call the 
function but I guess that is not unreasonable.

Thanks for your help and to everyone who answered this thread.

Regards
Matthew.

Euler Taveira de Oliveira wrote:
> Matthew Lunnon wrote:
>
>> Ahh, sorry, I have been too aggressive with my cutting, I am running 
>> 8.2.6 and the function is below.
>>
> <snip>
>
>> $BODY$
>>  LANGUAGE 'sql' VOLATILE;
>                  ^^^^^^^^^^
> I suspect that it's because you're using VOLATILE (so no good 
> optimizations is done); did you try STABLE? Could you show us the 
> EXPLAIN ANALYZE of query and function?
>
>


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-01-29 18:08:50
Subject: Re: JDBC/Stored procedure performance issue
Previous:From: Kevin GrittnerDate: 2008-01-29 17:13:54
Subject: Re: RAID arrays and performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group