Message-ID: <3F942558.2000804@joeconway.com>
Date: Mon, 20 Oct 2003 11:11:36 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
Cc: Michael Pohl <pgsql@newtopia.com>, pgsql-sql@postgresql.org,
	"George A.J" <jinujosein@yahoo.com>
Subject: Re: Which is faster SQL or PL/PGSQL
References: <Pine.LNX.4.44.0310201119240.10378-100000@server1.techplant.com>
	<200310201809.51830.dev@archonet.com>
	<3F941A43.1040802@joeconway.com>
	<200310201849.19668.dev@archonet.com>
In-Reply-To: <200310201849.19668.dev@archonet.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Richard Huxton wrote:
> On Monday 20 October 2003 18:24, Joe Conway wrote:
>>This question gets even more complex in 7.4, where many simple SQL
>>functions will get inlined, and library preloading is available to speed
>>that first PL/pgSQL call.
> 
> What will be the effects of inlining? Does it mean the planner merges the 
> function's plan into the larger query?
> 

Yes, I believe so (well, actually the optimizer). An inlined SQL 
function ends up behaving like a macro that expands at run time and is 
therefore quite fast -- no function call overhead at all.

Here is the comment from the source (src/backend/optimizer/util/clauses.c):

/*
  * inline_function: try to expand a function call inline
  *
  * If the function is a sufficiently simple SQL-language function
  * (just "SELECT expression"), then we can inline it and avoid the
  * rather high per-call overhead of SQL functions.  Furthermore, this
  * can expose opportunities for constant-folding within the function
  * expression.
  *
  * We have to beware of some special cases however.  A directly or
  * indirectly recursive function would cause us to recurse forever,
  * so we keep track of which functions we are already expanding and
  * do not re-expand them.  Also, if a parameter is used more than once
  * in the SQL-function body, we require it not to contain any volatile
  * functions (volatiles might deliver inconsistent answers) nor to be
  * unreasonably expensive to evaluate.  The expensiveness check not only
  * prevents us from doing multiple evaluations of an expensive parameter
  * at runtime, but is a safety value to limit growth of an expression
  * due to repeated inlining.
  *
  * We must also beware of changing the volatility or strictness status
  * of functions by inlining them.
  *
  * Returns a simplified expression if successful, or NULL if cannot
  * simplify the function.
  */

Joe