From: Craig Ringer <craig@postnewspapers.com.au>
To: Reuven M. Lerner <reuven@lerner.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL functions vs. PL/PgSQL functions
Date: Wed, 13 Oct 2010 16:11:48 +0800
Message-ID: <4CB569C4.7010100@postnewspapers.com.au> (raw)
In-Reply-To: <5549b04ab43625e39f74a859a2801ec3.squirrel@squirrelmail.lerner.co.il>
References: <5549b04ab43625e39f74a859a2801ec3.squirrel@squirrelmail.lerner.co.il>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, reuven@lerner.co.il
Subject: Re: SQL functions vs. PL/PgSQL functions
In-Reply-To: <4CB569C4.7010100@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox