Re: SQL functions vs. PL/PgSQL functions

"Reuven M. Lerner" <reuven(at)lerner(dot)co(dot)il> 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