Re: Performance problems inside a stored procedure.

[Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk>]

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?