1

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

Virgil_R
  • 71
  • 1
  • 2
  • 5
  • 80ms or 1500ms, that's a huge difference! Are you sure it takes over 1400ms to plan a (dynamic) statement? To me it looks like you have another problem, for example a missing connection pool. We use hundreds of SP's that execute dynamic statements within milliseconds on a busy 5TB database. – Frank Heikens Feb 28 '15 at 07:34
  • 1
    currently we're still on SQL Server, and there with "SET STATISTICS TIME ON" you can see exactly the "parse and compile time" besides "execution time", execution time was always ~ 80ms. – Virgil_R Mar 02 '15 at 14:47
  • Have you found the answer? – Yevgeniy Afanasyev Oct 10 '17 at 04:35
  • No, not really... Anyway, the management abandoned the Postgres idea, they moved into "let's switch to MySQL now...". In the meantime, SQL Server is doing a pretty good job... :-) Interesting observation (in my opinion) about PostgreSQL/MySQL communities: when you come up with questions about capabilities you have in SQL Server that don't exist in the other products, those "communities" would try to convince you first that you don't really need them.. LOL. And then, after a while, those products evolve and they present them as huge achievements – Virgil_R Nov 07 '17 at 15:05
  • So do you have your answer here? – Erwin Brandstetter Jan 23 '18 at 23:12

1 Answers1

2

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:

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:

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:

Related:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I disagree with that - if in my situation there are ~ 100 different possible queries that can be generated (because of the ~ 100 clients), and all 100 execution plans get cached (in SQL Server) but in PostgreSQL the server will spend time recompiling each time that code is called??? Can I used prepared statements if there are other parameters to the query than "Client ID"? or the query has to be completely identical? – Virgil_R Feb 27 '15 at 22:08
  • 1
    You seem to have a limited set of queries that are going to be reused many times. Not truly dynamic. Unless there are other hidden problems, it might be best to `PREPARE` queries dynamically and reuse prepared queries. *Parameters:* you can parameterize *anything* that is a value (not code or identifiers) and use as many parameters as you want. – Erwin Brandstetter Feb 27 '15 at 22:25
  • Lowering join_collapse_limit may yield terrible plans, in my experience (statistics might help, though) A good way to *hint* the optimiser is to use CTE's just to stay under the join_collapse_limit in each chunk. But preparing and reusing is always better, of course, – wildplasser Feb 28 '15 at 13:30
  • @wildplasser: Generally lowering `join_collapse_limit` would be a bad idea (except for special needs). But for queries with many joins, where you *know* the best (a good) sequence of joins, it may pay to `SET LOCAL join_collapse_limit = 1` for the transaction. Example: http://stackoverflow.com/questions/25044070/how-to-optimize-query-postgres/25044257#25044257 – Erwin Brandstetter Feb 28 '15 at 17:15
  • My guess is that the OP has an ugly (generated) query with a very large range table. But he is not showing it to us. – wildplasser Feb 28 '15 at 17:35