No execution plan caching for dynamic SQL in Postg

Question

This is just a prospective question, at work we're thinking of moving from SQL Server to PostgreSQL, please tell me I didn't understand this correctly from the PostgreSQL 9.4 documentation: "There is no plan caching for commands executed via EXECUTE".

We have a critical SP that builds a dynamic query and executes it, initially I didn't build the dynamic SQL correctly (the dynamic query was not parameterized) and because of that each time when this SP was hit, it spend ~ 1500ms to recompile the dynamic SQL (the query being built is quite massive) and ~ 80ms to execute it (the query is big but not very expensive).

After I re-wrote the code and parameterized it the execution plan(s) became very stable and now the SP normally needs only ~ 80ms, so this way I got rid of the recompilation performance issue once the query was parameterized (of course when the server reboots the execution plan cache needs some warming but a slow query happens now only once for each client (different data statistics for each client in different tables))

Please tell me that I didn't understand correctly the PostgreSQL 9.4 documentation or that maybe there is actually a way of caching the execution plan for dynamic SQL because going back to an execution time of ~ 1500ms wouldn't be acceptable and writing this SP with static SQL would require massive changes (for the developers) and that is definitely not an option...

Answer 1

Per documentation:

Also, there is no plan caching for commands executed via EXECUTE. Instead, the command is always planned each time the statement is run. Thus the command string can be dynamically created within the function to perform actions on different tables and columns.

That's a reasonable feature. The nature of a dynamic queries is that it changes its structure from call to call.

If you want to call the same query repeatedly (optionally with different parameters), use prepared statements:

• Split given string and prepare case statement

Or use plain (non-dynamic) SQL code inside plpgsql functions, those are treated like prepared statements as well.

In your case, it might be best to PREPARE queries dynamically. We had a very similar case here:

• Use text output from a function as new query

Also, it is extremely uncommon that a query spends 1.4 sec for planning and just 0.1 sec for execution. May be worth looking into. There may be ways to optimize. Like with join_collapse_limit:

• Sample Query to show Cardinality estimation error in PostgreSQL
• A: Slow fulltext search due to wildly inaccurate row estimates

Related:

• PostgreSQL Stored Procedure Performance
• How compiled are prepared statements?