Re: SQL functions vs. PL/PgSQL functions

On Wed, Oct 13, 2010 at 3:30 AM, Reuven M. Lerner <reuven@lerner.co.il> wrote:
> Hi, everyone.  I'm working with a client to try to optimize their use of
> PostgreSQL.  They're running 8.3 on a Windows platform, packaged as part
> of a physical product that is delivered to customers.
>
> We're planning to upgrade to 9.0 at some point in the coming months, but
> this question is relevant for 8.3 (and perhaps beyond).
>
> All of the database-related logic for this application is in server-side
> functions, written in PL/PgSQL.  That is, the application never issues a
> SELECT or INSERT; rather, it invokes a function with parameters, and the
> function handles the query.   It's not unusual for a function to invoke
> one or more other PL/PgSQL functions as part of its execution.
>
> Since many of these PL/PgSQL functions are just acting as wrappers around
> queries, I thought that it would be a cheap speedup for us to change some
> of them to SQL functions, rather than PL/PgSQL.  After all, PL/PgSQL is (I
> thought) interpreted, whereas SQL functions can be inlined and handled
> directly by the optimizer and such.
>
> We made the change to one or two functions, and were rather surprised to
> see the performance drop by quite a bit.
>
> My question is whether this is somehow to be expected.  Under what
> conditions will SQL functions be slower than PL/PgSQL functions?  Is there
> a heuristic that I can/should use to know this in advance?  Does it matter
> if the SELECT being executed operates against a table, or a PL/PgSQL
> function?
>
> Thanks in advance for any insights everyone can offer.

*) SQL functions require you to use $n notation for input arguments vs
the argument name.
*) SQL functions are fairly transparent to planning/execution. They
are re-planned every time they are run (as are views)
*) simple SQL functions can be inlined, allowing for much smarter
plans where they are called (especially if they are immutable/stable)
*) SQL functions are much more forcefully validated when created.
This is of course very nice, but can occasionally be a pain, if you
want the function to apply to a search path other than the default
search path.  This forces me to disable body checking in particular
cases.
*) In the not so old days, SQL functions could be called in more
conexts (select func() vs select * from func()). This is now changed
though.
*) SQL returning setof functions, can send RETURNING from
insert/update to the output of the function.  This is the ONLY way to
do this at present (until we get wCTE) w/o involving the client.

*) plpgsql functions are completely planned and that plan is held for
the duration of the session, or until a invalidation event occurs
(statistics driven, table dropping, etc).  This adds overhead to first
call but reduces overhead in subsequent calls since you don't have to
re-plan.  This also means you can't float the function over multiple
search paths on the same connection (EVER, even if you DISCARD).  This
also means you have to be aware of temp table interactions w/plans if
you are concerned about performance.
*) plpgsql allows dynamic execution (can use to get around above),
specific variable names, sane error handling, and all kinds of other
wonderful things too numerous to mention.
*) plpgsql simple expressions (like n:=n+1) can bypass SPI, and
therefore run pretty quickly.

both sql and plpgsql functions create a mvcc snapshot as soon as the
function is entered.  This can and will cause headaches if you are
writing highly concurrent systems utilizing serializable transactions.
(this is one of the biggest annoyances with a 100% pl interface to
your db).

when you make the jump to 9.0, you might want to check out libpqtypes
if you are writing your client in C.  it will greatly easy sending
complex data to/from the database to receiving functions.  certain
other db interfaces can also do this, for example python has a very
good database driver for postgres.

merlin