From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
To: Euler Taveira de Oliveira <euler@timbira.com>
Cc: pgsql-performance@postgresql.org, Heikki Linnakangas <heikki@enterprisedb.com>
Subject: Re: Performance problems inside a stored procedure.
Date: Tue, 29 Jan 2008 17:38:13 +0000
Message-ID: <479F6485.9@rwa-net.co.uk> (raw)
In-Reply-To: <479F3137.2070708@timbira.com>
References: <479DC452.8010400@rwa-net.co.uk>
<479DC558.30409@enterprisedb.com>
<479DC630.9010707@rwa-net.co.uk>
<479F3137.2070708@timbira.com>
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?
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mlunnon@rwa-net.co.uk, euler@timbira.com, heikki@enterprisedb.com
Subject: Re: Performance problems inside a stored procedure.
In-Reply-To: <479F6485.9@rwa-net.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox