问题的历史
PostgreSQL 引入了预编译语句，以消除重复执行 SQL 查询时的解析和规划开销。早期版本总是生成针对特定参数值的自定义执行计划，但这对于复杂查询带来了巨大的 CPU 成本。为了优化这一点，PostgreSQL 9.2 实施了计划缓存，通过通用计划机制在多次执行中重用单一计划结构。虽然这种方法减少了规划延迟，但在初始规划阶段，它将所有参数视为不透明的占位符。
问题
PostgreSQL 中的分区修剪在两个不同阶段进行：计划时修剪，即在查询规划期间，当规划器检查分区约束与字面值时，以及运行时修剪，即在执行期间使用分区连接或附加节点过滤来过滤分区。在计划时，针对预编译语句生成的通用计划缺乏具体的参数值，这使得计划时修剪变得不可能。因此，规划器生成对所有分区的扫描，无论在执行期间提供的实际参数值是什么，导致在大型分区表上的性能严重下降。
解决方案
解决方案需要强制 PostgreSQL 在规划阶段生成自定义计划，以包含实际的参数值。可以通过将 plan_cache_mode 配置参数设置为 force_custom_plan 来实现，该设置适用于会话或特定查询，完全绕过通用计划缓存。或者，协议级别的解决方案包括使用包含字面值而非参数的 Bind 消息的 扩展查询协议，或者使用客户端查询生成器在内联字面值的同时保持其他参数绑定以防止 SQL 注入。
-- 强制为此会话生成自定义计划 SET plan_cache_mode = force_custom_plan; -- 或使用 format() 的动态 SQL 安全地内联字面值 EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
一个高频交易分析平台在每天早晨查询当天价格波动时经历了严重的延迟高峰。数据库将 tick 数据存储在按交易会话日期分区的表中，包含超过 2,000 个跨度五年的分区。应用程序使用 JDBC 预编译语句，使用 ? 占位符作为日期参数，以防止 SQL 注入并减少解析开销。
开发团队最初观察到，过滤“今天”数据的查询正在扫描历史分区，消耗了 45 秒而不是预期的 300 毫秒。这种性能下降发生是因为通用计划无法在规划阶段消除无关的分区。
一种方法涉及为热数据创建一个单独的未记录表，并在每晚迁移记录。这种策略将完全绕过分区表进行最新查询，但引入了复杂的 ETL 逻辑，并且在系统崩溃时存在数据丢失的风险。
另一种提议建议在 JDBC 连接池中全局禁用预编译语句。虽然这样会通过向规划器公开字面值来恢复计划时修剪，但基准测试显示，由于重复的解析和规划开销，数据库服务器的 CPU 利用率增加了 40%。
团队还评估了使用 PostgreSQL 在版本 11 引入的运行时分区修剪功能。然而，运行时修剪仅在执行器开始扫描后才会消除分区，这意味着规划器仍然分配资源用于所有分区，并产生忽略分区边界的次优连接顺序。
最终，团队选择实施连接级配置切换。他们配置连接池以检测针对分区表的查询，并在调度这些特定语句之前执行 SET plan_cache_mode = force_custom_plan。这保持了参数化查询对用户输入过滤器的安全性，同时确保分区键值对规划器可见。
结果将查询延迟降低至 280 毫秒，并将整体数据库 CPU 使用率降低了 15%，因为规划器现在可以利用约束排除，在执行之前消除 1,999 个分区。这种优化使交易平台能够在不妥协数据完整性或安全性的情况下满足严格的晨间延迟要求。
在 plan_cache_mode 设置为 auto 时，PostgreSQL 如何选择通用计划和自定义计划？
在 auto 模式下，PostgreSQL 为前五次执行使用自定义计划进行规划和执行，积累规划成本。在第五次执行后，它将生成计划的平均执行时间（在第一次执行时估计）与自定义计划及其规划开销的平均执行时间进行比较。如果通用计划的估计成本低于自定义计划的平均成本，系统将为该预编译语句永久切换到通用计划。候选人常常忽略此比较包括重新使用通用计划所节省的计划开销，并且该决策在预编译语句的生命周期内是永久性的，除非明确重新规划。
在预编译语句的上下文中，计划时修剪和运行时修剪之间有什么区别？
计划时修剪发生在规划阶段，当规划器可以证明某些分区基于分区约束和查询中的字面值不能包含相关行时。运行时修剪发生在执行期间，当执行器使用 执行器 的过滤机制检查实际参数值对分区约束的符合性。自 PostgreSQL 11 起，通用预编译计划支持运行时修剪，但由于参数值未知，它们无法支持计划时修剪。候选人经常混淆这些机制，认为运行时修剪解决了所有预编译语句的分区问题，而不清楚计划时修剪对于有效的连接计划和索引选择至关重要。
为什么 force_custom_plan 可能无法解决 PostgreSQL 10 之前的分区修剪问题？
在版本 10 之前，PostgreSQL 对运行时分区修剪完全缺乏支持，如果通过使用包含参数的 Bind 消息的 扩展查询协议 传递参数，即使是自定义计划也无法受益于约束排除。规划器将所有绑定参数视为外部于规划过程，必须在查询字符串中显式包含字面值才能触发约束排除。这一历史限制意味着在传统系统中，即使是自定义计划也会扫描所有分区，必须使用带字面值的 EXECUTE ... USING 动态 SQL 生成，或者使用适当转义的客户端字符串连接，而不是现代协议级参数绑定。