Re: SQL functions vs. PL/PgSQL functions

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/