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.
Reuven
--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner