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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
