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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I was told that Queries get their execution plan cached here
@YevgeniyAfanasyev: That's a misunderstanding. The query plan for ad-hoc SQL statements is not cached. Only for prepared statements.
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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.