Message-ID: <4CB569C4.7010100@postnewspapers.com.au>
Date: Wed, 13 Oct 2010 16:11:48 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB;
	rv:1.9.1.12) Gecko/20100914 Thunderbird/3.0.8
MIME-Version: 1.0
To: "Reuven M. Lerner" <reuven@lerner.co.il>
CC: pgsql-performance@postgresql.org
Subject: Re: SQL functions vs. PL/PgSQL functions
References: 
 <5549b04ab43625e39f74a859a2801ec3.squirrel@squirrelmail.lerner.co.il>
In-Reply-To: 
 <5549b04ab43625e39f74a859a2801ec3.squirrel@squirrelmail.lerner.co.il>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 13/10/2010 3:30 PM, Reuven M. Lerner wrote:

> My question is whether this is somehow to be expected.  Under what
> conditions will SQL functions be slower than PL/PgSQL functions?

The main cases I can think of:

- Where the SQL function is inlined (PL/PgSQL functions can't be 
inlined, some SQL functions can) and the inlining turns out to be a 
performance loss rather than a gain.

- Where the PL/PgSQL function was constructing queries dynamically for 
EXECUTE ... USING, so each query contained its parameters directly. If 
converted to an SQL function (or a PL/PgSQL function using SELECT / 
PERFORM instead of EXECUTE ... USING) the planner will make more generic 
choices because it doesn't have stats on specific parameter values. 
These choices are sometimes not all that great.

Beyond that, I'd have to wait to hear from someone who has more real 
knowledge than my hand-waving can provide.

-- 
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/