Re: Performance problems inside a stored procedure.

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