PostgreSQL 计划缓存实战（第 10 篇）：预编译 SQL 前五次都快，第六次为什么可能变慢

普通租户只有百条订单头部租户却有九十万条。相同 prepared statement 前几次都快某个连接复用后头部租户突然用索引扫描九十万行重连又恢复。直接答案是这个会话可能在累计五个 custom plan 后开始评估 generic plan而 generic plan 看不到本次参数冷热租户差异便被平均分布掩盖。Custom plan 看得到本次参数generic plan 省掉重复规划却只能按平均分布估算。参数倾斜越强省下的规划毫秒越可能换来执行秒级退化。先纠正“第六次必切换”plan_cache_mode auto时PostgreSQL 对带参数的 prepared statement 前五次使用 custom plan并计算其平均估算成本之后生成 generic plan将其成本与平均 custom 成本比较。如果重复规划看起来不值得后续才使用 generic plan。因此第六次是开始具备选择 generic 的条件不是必然切换最终可能继续 customDDL、统计更新等会触发重新分析/规划每个数据库会话拥有自己的 prepared statement 与计数历史。源码证明比较的不是前五次真实耗时PostgreSQL 18.6 的核心入口位于src/backend/utils/cache/plancache.c的choose_custom_plan()。去掉强制模式、一次性计划等前置分支后决策等价于# 逻辑等价伪代码不是可编译源码 num_custom_plans 5 → custom avg_custom_cost total_custom_cost / num_custom_plans generic_cost avg_custom_cost → generic 其他情况 → custom这里有两个容易写错的细节比较的是 planner 的估算成本不是前五次的真实执行时间custom cost 会计入重复规划的估算开销generic cost 不计每次重规划因此 generic 只要在“执行估算 重规划代价”的总账上更便宜就可能胜出。第一次尝试 generic 时generic_cost尚未确定代码会先构建 generic plan、记录成本再调用一次choose_custom_plan()复核如果 custom 一直明显占优这个刚生成的 generic plan 不会被实际执行。因此“第六次开始评估”比“第六次执行 generic”更准确。PREPARE/EXECUTE → GetCachedPlan() → choose_custom_plan() → num_custom_plans、total_custom_cost、generic_cost → custom 或 generic → pg_prepared_statements.generic_plans/custom_plans本文不补无关 Java 示例要证明的是 PostgreSQL 服务端计划缓存决策SQLPREPARE/EXECUTE是最直接入口。JDBC 驱动的 server-side prepare 阈值属于调用前置条件必须在生产排查时另行确认不能代替服务端证据。构造冷热租户DROPTABLEIFEXISTStenant_order;CREATETABLEtenant_order(idbigintPRIMARYKEY,tenant_idbigintNOTNULL,payloadtextNOTNULL);-- 热租户 1900000 行INSERTINTOtenant_orderSELECTg,1,repeat(h,100)FROMgenerate_series(1,900000)ASg;-- 1000 个冷租户每个约 100 行INSERTINTOtenant_orderSELECT900000g,2((g-1)%1000),repeat(c,100)FROMgenerate_series(1,100000)ASg;CREATEINDEXtenant_order_tenant_idxONtenant_order(tenant_id);ANALYZEtenant_order;PREPAREtenant_q(bigint)ASSELECTsum(length(payload))FROMtenant_orderWHEREtenant_id$1;先做确定性对照SETplan_cache_modeforce_custom_plan;EXPLAIN(ANALYZE,BUFFERS,SETTINGS)EXECUTEtenant_q(2);EXPLAIN(ANALYZE,BUFFERS,SETTINGS)EXECUTEtenant_q(1);SETplan_cache_modeforce_generic_plan;EXPLAIN(ANALYZE,BUFFERS,SETTINGS)EXECUTEtenant_q(2);EXPLAIN(ANALYZE,BUFFERS,SETTINGS)EXECUTEtenant_q(1);Custom plan 对冷租户通常适合索引路径对 90% 行都命中的热租户可能选择顺序扫描。Generic plan 中会保留$1无法知道这次是头部租户可能按平均每租户行数选择索引路径导致热参数大量随机/重复 heap 访问。实际计划取决于缓存、成本参数和数据宽度。这个实验的判定不是强求某个节点而是比较同一参数在 custom/generic 下的行数估算、Buffers 与耗时。恢复自动模式重新准备以清空该语句的计划历史DEALLOCATEtenant_q;SETplan_cache_modeauto;PREPAREtenant_q(bigint)ASSELECTsum(length(payload))FROMtenant_orderWHEREtenant_id$1;EXECUTEtenant_q(2);EXECUTEtenant_q(3);EXECUTEtenant_q(4);EXECUTEtenant_q(5);EXECUTEtenant_q(6);EXPLAIN(ANALYZE,BUFFERS,SETTINGS)EXECUTEtenant_q(1);SELECTname,generic_plans,custom_plans,statementFROMpg_prepared_statementsWHEREnametenant_q;前五次全是冷租户会影响平均 custom 成本第六次热参数可能遇到 generic也可能算法仍判定 custom 更好。generic_plans/custom_plans是事实证据不能仅凭“恰好第六次慢”倒推。还应记录同一次观察前后的计数差而不是只看最终总数SELECTname,generic_plans,custom_plansFROMpg_prepared_statementsWHEREnametenant_q;EXPLAIN(ANALYZE,BUFFERS,SETTINGS)EXECUTEtenant_q(1);SELECTname,generic_plans,custom_plansFROMpg_prepared_statementsWHEREnametenant_q;若generic_plans增加说明这次取得了 generic plan若只看到$1也应与计数交叉验证避免把展示差异当成完整会话历史。为什么连接池让故障像随机事件Prepared statement 是 session 对象。连接池中的每个后端经历不同参数序列连接 A冷、冷、冷、冷、冷 → generic → 热参数退化 连接 B热、热、冷、热、冷 → custom 平均成本不同 连接 C刚重建连接 → 重新从 custom 开始应用日志看到同一 SQL数据库看到的是多份会话级计划历史。pg_prepared_statements也只显示当前会话可见对象不能从一个管理连接观察整个池。还要确认驱动是否真的创建 server-side prepared statement、准备阈值是多少、事务池化是否保留 session以及代理是否改写连接语义。客户端叫“预编译”不自动等于 PostgreSQLPREPARE。四种处理路径路径适用条件代价与边界保持auto参数分布较均匀规划成本值得节省极端热点可能被平均值掩盖局部force_custom_plan参数强烈决定路径、单次执行较重每次支付规划 CPU拆分冷热 SQL/连接热租户可稳定识别应用路由与观测复杂度上升索引、分区或模型治理倾斜是长期业务事实变更成本高但可能根治访问路径不要全局强制 custom。高 QPS、执行极短的语句可能把大量 CPU 浪费在重复规划也不要用DEALLOCATE或重连作为长期修复它只重置历史退化可能再次出现。生产证据链在同一会话、同一参数下分别强制 custom/generic比较计划与执行。看$1是否仍出现在计划中结合generic_plans/custom_plans确认类型。找第一处 estimated/actual rows 分叉确认租户热点是否进入统计。核对驱动、连接池、代理和 prepared threshold。用pg_stat_statements比较 calls、planning/execution time 与波动但注意它不会直接替代会话级计划证据。在真实参数分布和并发下计算“规划 CPU 执行成本”的总账。如果第一处分叉来自列相关性或数据分布估错先阅读第 9 篇SQL 和索引没变计划为什么突然慢一百倍修复统计证据计划缓存不能替代基数估算治理。灰度与回滚优先对专用报表角色、事务或单个连接设置BEGIN;SETLOCALplan_cache_modeforce_custom_plan;-- 目标查询COMMIT;灰度记录热/冷参数 p95、规划 CPU、数据库总 CPU 和连接池吞吐。若规划时间或整体 CPU 超过停止阈值停止扩大范围。恢复auto可撤销设置但不能自动修复数据倾斜和索引模型。证据边界证据能证明不能证明第六次变慢与启发式时点吻合一定已经切 generic计划中出现$1当前展示的是 generic plan所有连接都使用 generic强制 custom 更快参数感知对该值有收益全局 custom 总成本更低重连恢复session 状态参与故障根因只有 plan cache统计显示热点planner 有热点信息generic 能使用本次参数值面试表达主线Prepared statement 可以用参数感知 custom plan也可复用 generic plan。Auto 前五次采样 custom 成本之后比较 generic 与平均 custom 成本并非第六次必切。参数倾斜时要在同一会话对比两类计划并结合驱动、连接池和规划 CPU 做局部治理。实验清理DEALLOCATEtenant_q;RESET plan_cache_mode;DROPTABLEIFEXISTStenant_order;官方资料PostgreSQL 18PREPAREPostgreSQL 18plan_cache_modePostgreSQL 18pg_prepared_statementsPostgreSQL 18EXPLAINPostgreSQL 18.6 源码plancache.c / choose_custom_plan()PostgreSQL 18.6 源码标签 REL_18_6