On Wed, Oct 13, 2010 at 3:30 AM, Reuven M. Lerner <reuven@xxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
