Search code examples
postgresqlprepared-statementplpgsqlsql-execution-plan

PostgreSQL performance of ad-hoc SQL vs functions

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?

Solution

  • 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.