PostgreSQL 9.4中没有针对动态SQL的执行计划缓存？ [英] No execution plan caching for dynamic SQL in PostgreSQL 9.4?
问题描述
这只是一个预期的问题，在工作中，我们正在考虑从SQL Server迁移到PostgreSQL，请告诉我我对PostgreSQL 9.4文档的理解不正确：
没有计划缓存对于通过EXECUTE执行的命令。
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".
我们有一个关键的SP，可以构建并执行动态查询，起初我没有正确地构建动态SQL（动态查询没有参数化），因此每次击中此SP时，它花费约1500ms来重新编译动态SQL（正在构建的查询非常庞大）和花费80ms来执行它（查询很大但不是很昂贵）
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).
重新编写代码并将其参数化后，执行计划变得非常稳定，现在SP通常仅需要80毫秒，因此我得到了解决了查询参数化后的重新编译性能问题（当然，当服务器重新启动时，执行计划缓存需要进行一些预热，但现在会发生缓慢的查询每个客户端一次（不同客户端在不同表中的数据统计信息）
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))
请告诉我我对PostgreSQL 9.4文档的理解不正确，或者实际上，这是一种缓存动态SQL执行计划的方式，因为回到〜1500毫秒的执行时间是不可接受的，并且使用静态SQL编写此SP需要进行大量更改（对于开发人员），这绝对不是一种选择...
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...
推荐答案
也，对于通过
EXECUTE执行的命令，没有没有计划缓存。
而是总是在每次运行语句时计划命令。
因此，可以在函数
中动态创建命令字符串，以对不同的表和列执行操作。
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
或者在plpgsql函数中使用普通的（非动态）SQL代码，这些代码也像预处理语句一样对待。
Or use plain (non-dynamic) SQL code inside plpgsql functions, those are treated like prepared statements as well.
在您的情况下，最好动态地进行 PREPARE 查询。我们在这里有一个非常相似的情况：
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
此外，极端非常罕见，查询花费1.4秒钟进行计划，执行仅花费0.1秒钟。可能值得研究。可能存在优化的方法。类似于 join_collapse_limit ：
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
相关：
- PostgreSQL Stored Procedure Performance
- How compiled are prepared statements?
这篇关于PostgreSQL 9.4中没有针对动态SQL的执行计划缓存？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
