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 quer

Solution 1:

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:

• No execution plan caching for dynamic SQL in PostgreSQL 9.4?

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

• Monitoring the Postgres query parser

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