Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id AEE362E01D1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 29 Jan 2008 13:38:17 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 46794-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 29 Jan 2008 13:38:11 -0400 (AST)
Received: from mail-relay.rwa-net.co.uk (mail-relay.rwa-net.co.uk
	[62.189.139.18])
	by postgresql.org (Postfix) with ESMTP id B0B322E026F
	for <pgsql-performance@postgresql.org>;
	Tue, 29 Jan 2008 13:38:11 -0400 (AST)
Received: from saturn.rwa-net.co.uk (saturn.rwa-net.co.uk [62.189.139.33])
	by mail-relay.rwa-net.co.uk (Postfix) with ESMTP id D26917D6375
	for <pgsql-performance@postgresql.org>;
	Tue, 29 Jan 2008 17:17:08 +0000 (GMT)
Received: from [127.0.0.1] by saturn.rwa-net.co.uk (GMS
	14.01.3539/NT4698.00.83343e8d) with ESMTP id rcikgaaa for
	pgsql-performance@postgresql.org; Tue, 29 Jan 2008 17:38:09 +0000
Message-ID: <479F6485.9@rwa-net.co.uk>
Date: Tue, 29 Jan 2008 17:38:13 +0000
From: Matthew Lunnon <mlunnon@rwa-net.co.uk>
Reply-To: mlunnon@rwa-net.co.uk
Organization: RWA
User-Agent: Thunderbird 1.5.0.13 (Windows/20070809)
MIME-Version: 1.0
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.
References: <479DC452.8010400@rwa-net.co.uk> <479DC558.30409@enterprisedb.com>
	<479DC630.9010707@rwa-net.co.uk> <479F3137.2070708@timbira.com>
In-Reply-To: <479F3137.2070708@timbira.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-AuthenticatedSender: mlunnon@rwa-net.co.uk
X-Info: UNKNOWN-USER
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200801/295
X-Sequence-Number: 28534

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