From: Merlin Moncure <mmoncure@gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
To: Simon Dale <sdale@rm.com>
To: pgsql-performance@postgresql.org
Subject: Re: Stored Procedure Performance
Date: Tue, 11 Apr 2006 17:02:00 -0400
Message-ID: <b42b73150604111402n66e4ee8j1ba7daf85446c86a@mail.gmail.com> (raw)
In-Reply-To: <20060411140857.GE11760@surnet.cl>
References: <416F947BF5BA9E40B5F9AA649422212E074DE930@ex-mail2.internal.rmplc.net>
<b42b73150604110649r3443a561x48dba1b9dc28703f@mail.gmail.com>
<20060411140857.GE11760@surnet.cl>
On 4/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Merlin Moncure wrote:
> > pl/pgsql procedures are a very thin layer over the query engine.
> > Generally, they run about the same speed as SQL but you are not making
> > apples to apples comparison. One of the few but annoying limitations
> > of pl/pgsql procedures is that you can't return a select directly from
> > the query engine but have to go through the return/return next
> > paradigm which will be slower than raw query for obvious reasons.
>
> There's one problem that hasn't been mentioned. For the optimizer a
> PL/pgSQL function (really, a function in any language except SQL) is a
> black box. If you have a complex join of two or three functions, and
> they don't return 1000 rows, it's very likely that the optimizer is
> going to get it wrong.
This doesn't bother me that much. Those cases usually have a high
overlap with views.You just have to plan on the function being fully
materialized before it is inovled further. What drives me crazy is I
have to do 'select * from plpgsql_srf()' but I am allowed to do the
much friendlier and more versatile 'select sql_srf()', even if they do
more or less the same thing.
On the flip side, what drives me crazy about sql functions is that all
tables have to be in the search path for the validator. Since I
frequently use the trick of having multiple schemas with one set of
functions this is annoying.
Merlin
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: mmoncure@gmail.com, sdale@rm.com
Subject: Re: Stored Procedure Performance
In-Reply-To: <b42b73150604111402n66e4ee8j1ba7daf85446c86a@mail.gmail.com>
* 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