DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=EzmEtJg9f/bwL25qYK5lwjB1R5XLzu3cUmrkveHX928k9yF27sCPujfq3wMS4yeRXbB0R4fataYiNEamlWpJCmrHHzlEnsfUa7/clcuFHr+RFfKSGkHXBdCmlk+fOeF3uBN/m0bpxpsiemGFQb1mjAaLRBrmjvEWj1x/kZWXiIU=
Message-ID: <45F04D62.8090302@enterprisedb.com>
Date: Thu, 08 Mar 2007 17:52:34 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.8 (X11/20061116)
MIME-Version: 1.0
To: "Schwarz, Karl" <Karl.Schwarz@ngc.com>
CC: pgsql-performance@postgresql.org
Subject: Re: function performance vs in-line sql
References: <9E64800D28DF264DB32B0309B6F222D5188548@xcgny105.northgrum.com>
In-Reply-To: <9E64800D28DF264DB32B0309B6F222D5188548@xcgny105.northgrum.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Schwarz, Karl wrote:
> I am having a performance problem with a query implemented within a
> server side function. If I use an SQL client(EMS Postgres) and manually
> generate the sql query I get about 100 times performance improvement
> over using the function.
> 
> I've also tried using a prepared statement from my application  and
> observed a similar performance improvement over the the function.
> 
> The table I am quering against has several hundred thousand records. I
> have indexes defined and I've run vacuum several times. 
> 
> Is there something basic I am missing here with the use of a function. I
> am no database expert, but my assumption was that a function would give
> me better results than in-line sql.
> 
> I've seen a mailing list entry in another list that implied that the
> query planner for a function behaves differently than in-line sql. 

For starters, can you show us the function, the manual sql query and the 
schema, please?

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com