Re: Which is faster SQL or PL/PGSQL

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
haveto 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
parameteris used more than once * in the SQL-function body, we require it not to contain any volatile * functions
(volatilesmight deliver inconsistent answers) nor to be * unreasonably expensive to evaluate.  The expensiveness check
notonly * prevents us from doing multiple evaluations of an expensive parameter * at runtime, but is a safety value to
limitgrowth of an expression * due to repeated inlining. * * We must also beware of changing the volatility or
strictnessstatus * of functions by inlining them. * * Returns a simplified expression if successful, or NULL if cannot
*simplify the function. */
 

Joe