1

Is there any difference? I know SQL queries are having their execution plans cached just as good as functions.

I foud someone telling:

Performance is an issue, and we suspect query planning might be an underlying cause. I've rewritten the scripts from ad-hoc SQL to a Postgres functions (CREATE FUNCTION) and we saw server load go down quite a bit.

But why?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Yevgeniy Afanasyev
  • 37,872
  • 26
  • 173
  • 191

1 Answers1

4

The query plan for ad-hoc queries is not cached, only for prepared statements. And PL/pgSQL functions handle all SQL statements like prepared statements internally. (With the notable exception of dynamic SQL with EXECUTE.) Each for the scope of the current session, not beyond.

So PL/pgSQL functions (not SQL functions!) can help with repeated execution of sophisticated queries within the same session. Just like prepared statements.

Client software may be using prepared statements by default. Or the "extended query" protocol, to the same effect.

Related:

The related answer that started the thread on pgsql-general you are referring to:

Also consider the chapter Plan Caching for PL/pgSQL in the manual.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I was told that Queries get their execution plan cached [here](https://stackoverflow.com/questions/46677509/postgresql-function-execution-plan-cache-principle/46678190?noredirect=1#comment80305222_46678190) – Yevgeniy Afanasyev Oct 11 '17 at 02:21
  • 1
    @YevgeniyAfanasyev: That's a misunderstanding. The query plan for ad-hoc SQL statements is not cached. Only for prepared statements. – Erwin Brandstetter Oct 11 '17 at 02:25
  • Right. I was assuming you were using prepared statements, because if you were even *thinking* about the details of how plans are cached in functions etc, you would have already done that as the first step in reducing planning costs. Also, if the called function is an inlineable SQL function, it could be cached indirectly in calling plpgsql functions. – Craig Ringer Oct 11 '17 at 02:45
  • 1
    BTW, I think you're making a real mistake by focusing on planning time without (as far as you have shown) looking into how much planning time is actually impacting your performance. And you don't seem to be considering the *significant* overheads of doing work via the fmgr and plpgsql proc handlers etc, vs raw queries, and weighing that against planning costs. I strongly advise you not to pursue this further until you've made sure you're optimally using prepared statements across the app, at least. – Craig Ringer Oct 11 '17 at 02:47