Re: Stored Procedure Performance

On 4/11/06, Alvaro Herrera <alvherre(at)commandprompt(dot)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