"Reuven M. Lerner" <reuven@xxxxxxxxxxxx> writes: > 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. It's not particularly surprising, especially not if your past development has tended to tune the app so that plpgsql works well. In the first place, SQL operations issued in plpgsql aren't somehow "interpreted" when everything else is "compiled". It's the same execution engine. It would be fair to speak of control logic in plpgsql as being interpreted; but since SQL functions don't have any ability to include control logic at all, you're not going to be moving anything of that description over. Besides, the control logic usually takes next to no time compared to the SQL operations. The reason that plpgsql-issued queries are sometimes slower than queries executed directly is that plpgsql parameterizes the queries according to whatever plpgsql variables/parameters they use, and sometimes you get a worse plan if the planner can't see the exact values of particular variables used in a query. The reason plpgsql does that is that it saves the plans for individual SQL queries within a function for the life of the session. SQL functions involve no such state --- either they get inlined into the calling query, in which case they have to be planned when that query is, or else they are planned on-the-fly at beginning of execution. So your change has definitely de-optimized things in the sense of introducing more planning work. Now you could have seen a win anyway, if plpgsql's parameterized query plans were sufficiently inefficient that planning on-the-fly with actual variable values would beat them out. But that's evidently not the case for (most of?) your usage patterns. In places where it is the case, the usual advice is to fix it by using EXECUTE, not by giving up plpgsql's ability to cache plans everywhere else. It's possible that at some point we'll try to introduce plan caching for non-inlined SQL functions. But at best this would put them on a par with plpgsql speed-wise. Really the only place where a SQL function will be a win for performance is if it can be inlined into the calling query, and that's pretty much never the case in the usage pattern you're talking about. (The sort of inlining we're talking about is more or less textual substitution, and you can't insert an INSERT/UPDATE/DELETE in a SELECT.) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
