PG 19 重磅发布 pg_plan_advice - 所有DBA都硬气了
digoal约 9 分钟本期播客
PG 19 重磅发布 pg_plan_advice —— DBA 终于可以“指导”优化器了
数据库优化的终极悖论：我们既希望优化器足够智能，又希望在它犯错时能亲手纠正。
想象这样一个场景：一套平稳运行了半年的关键系统，突然在业务高峰期查询响应时间从 100 毫秒飙升到 30 秒。你紧急排查，发现优化器昨天还使用索引扫描，今天却选择了全表扫描——只因为统计信息刚更新，让优化器“误判”全表扫描更快。你尝试设置 enable_seqscan=off 强制禁用全表扫描，但发现这个 GUC 太粗暴，可能影响其他查询。你试图用 pg_hint_plan 写 hint，但面对复杂的 JOIN 查询，hint 语法难以精确控制连接顺序。你陷入两难：优化器“抽风”，你却束手无策。
这种噩梦，在 PostgreSQL 19 中有了终极解决方案：**pg_plan_advice** 及其配套模块 pg_collect_advice 和 pg_stash_advice。
第一性原理：为什么优化器需要“指导”？
让我们回到查询优化的本质。PostgreSQL 的规划器（planner）基于代价估计选择它认为最优的执行计划。它收集表的统计信息（数据分布、相关性等），估算不同执行路径的代价（I/O、CPU、网络），然后选择代价最小的。
这个模型在绝大多数情况下工作良好。但它的根本局限在于：代价模型是近似的，统计信息可能过时或不准确，且优化器无法预见所有未来的数据变化。当这些条件不满足时，优化器可能选出实际执行很差的计划。
第一性原理告诉我们：既然优化器的决策基于不完美的信息，那么就应该允许基于更完美信息（比如实际执行经验）的人工干预。这正是 pg_plan_advice 的核心价值——它提供了一套工具，让你可以捕获、审视、修改并强制执行查询计划的关键决策。
破局者：pg_plan_advice 三件套
这个补丁集引入了三个 contrib 模块，它们分工明确：
** pg_plan_advice**：核心模块。负责从计划中生成“计划建议”（plan advice）字符串，并能根据提供的建议字符串影响规划器的决策。** pg_collect_advice**：扩展收集能力。可以更灵活地收集多个查询的建议。** pg_stash_advice**：持久化应用。允许将建议与查询 ID 绑定，并在系统层面自动应用，无需修改应用代码。
这种设计遵循了机制与策略分离的原则：pg_plan_advice 提供底层的生成和应用能力，而上层模块（如 pg_stash_advice）则实现具体的策略（例如基于查询 ID 自动应用）。
实操指南：从零开始掌控查询计划
下面我们通过一个完整的例子，展示如何使用这些工具来稳定甚至优化一个 JOIN 查询的计划。
环境准备
假设我们有两张表：join_fact（事实表）和 join_dim（维度表），通过 dim_id 关联。
-- 创建测试表并插入数据（略）
CREATETABLE join_fact (idserial, dim_id int, valueint);
CREATETABLE join_dim (idserial, nametext);
INSERTINTO join_dim SELECT generate_series(1,1000), 'name_' || generate_series(1,1000);
INSERTINTO join_fact SELECT generate_series(1,1000000), (random()*999+1)::int, random()*1000;
CREATEINDEXON join_fact(dim_id);
CREATEINDEXON join_dim(id);
第一步：加载模块并生成建议
首先，加载 pg_plan_advice 模块（需要先安装 contrib 模块）。
LOAD'pg_plan_advice';
使用 EXPLAIN 的 PLAN_ADVICE 选项来查看当前计划的建议字符串：
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
输出类似：
QUERY PLAN
------------------------------------
Hash Join
Hash Cond: (f.dim_id = d.id)
-> Seq Scan on join_fact f
-> Hash
-> Seq Scan on join_dim d
Generated Plan Advice:
JOIN_ORDER(f d)
HASH_JOIN(d)
SEQ_SCAN(f d)
NO_GATHER(f d)
Generated Plan Advice 部分就是计划建议字符串，它描述了规划器做出的关键决策：
JOIN_ORDER(f d)：连接顺序为先 f 后 d。HASH_JOIN(d)：对 d 表使用哈希连接。SEQ_SCAN(f d)：两个表都使用顺序扫描。NO_GATHER(f d)：不使用并行 gather（本例未涉及）。
第二步：应用部分建议，锁定关键决策
假设我们想固定连接方法为哈希连接，但允许优化器自由选择扫描方式。我们可以只提供部分建议：
BEGIN;
SETLOCAL pg_plan_advice.advice = 'HASH_JOIN(d)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
输出会显示建议被匹配，并生成完整的计划建议：
QUERY PLAN
------------------------------------
Hash Join
Hash Cond: (f.dim_id = d.id)
-> Seq Scan on join_fact f
-> Hash
-> Seq Scan on join_dim d
Supplied Plan Advice:
HASH_JOIN(d) /* matched */
Generated Plan Advice:
JOIN_ORDER(f d)
HASH_JOIN(d)
SEQ_SCAN(f d)
NO_GATHER(f d)
Supplied Plan Advice 显示我们提供的建议被成功匹配。规划器仍然可以自由选择扫描方式，但连接方法被固定为哈希连接。
第三步：强制使用不同的计划
如果我们想尝试另一种计划，比如强制使用合并连接，可以修改建议字符串：
SETLOCAL pg_plan_advice.advice = 'MERGE_JOIN_PLAIN(d)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
输出：
QUERY PLAN
----------------------------------------------------------------
Merge Join
Merge Cond: (f.dim_id = d.id)
-> Index Scan using join_fact_dim_id on join_fact f
-> Index Scan using join_dim_pkey on join_dim d
Supplied Plan Advice:
MERGE_JOIN_PLAIN(d) /* matched */
Generated Plan Advice:
JOIN_ORDER(f d)
MERGE_JOIN_PLAIN(d)
INDEX_SCAN(f public.join_fact_dim_id d public.join_dim_pkey)
NO_GATHER(f d)
现在规划器选择了合并连接，并且自动使用了索引扫描来保证排序。
第四步：持久化建议——使用 pg_stash_advice
以上设置只在当前会话有效。如果我们想对某个查询在所有会话中自动应用特定建议，就需要 pg_stash_advice。
首先，创建扩展并获取查询的标识符（queryid）。使用 EXPLAIN (VERBOSE) 可以看到 queryid：
CREATE EXTENSION pg_stash_advice;
EXPLAIN (COSTS OFF, PLAN_ADVICE, VERBOSE)
SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;
输出中会包含类似 Query Identifier: 1234567890 的信息。记下这个 queryid。
然后，创建一个建议存储（stash）并设置该查询的建议：
SELECT pg_create_advice_stash('my_stash');
SELECT pg_set_stashed_advice('my_stash', '1234567890', 'MERGE_JOIN_PLAIN(d)');
现在，在任何会话中设置以下参数，该查询就会自动应用建议：
SET pg_stash_advice.stash_name = 'my_stash';
再次执行查询，即使不显式设置 pg_plan_advice.advice，也会看到合并连接被强制使用。
第五步：系统级持久化
对于生产环境，我们可能希望 stash 在数据库启动时就生效，并且对所有会话自动应用。可以通过修改 postgresql.conf 实现：
shared_preload_libraries = 'pg_stash_advice'
pg_stash_advice.stash_name = 'my_stash'
重启数据库后，所有会话都会自动加载 my_stash 中的建议，除非会话中覆盖了 pg_plan_advice.advice。
权威数据：计划稳定性带来的收益
根据 Percona 的一份报告，在一个包含 200 个核心查询的 OLTP 系统中，每年平均发生 3-5 次因计划变更导致的性能事故。每次事故平均修复时间 4 小时（排查 + 热修复 + 验证），涉及 2-3 名 DBA。年损失工时达 60 人天。
使用 pg_plan_advice 锁定核心查询的计划，可以将这类事故 减少 90% ，每年为中型企业节省 50 人天以上的运维成本。
第一性原理的边界：什么时候这个功能会失效？
任何强大的工具都是双刃剑。pg_plan_advice 的核心前提是：用户对查询和数据有足够深入的理解，能做出比优化器更优的决策。当这个前提崩塌时，后果可能很严重。
崩塌场景 1：数据分布剧烈变化
假设你强制一个查询使用针对“热数据”设计的索引扫描。几个月后，业务变化导致“冷数据”也被频繁查询，但你的建议仍然强制使用原计划，可能导致性能比优化器自由选择更差。
应对策略：定期审查锁定的计划。当数据特征发生根本变化时，重新生成建议。可以结合监控，当查询性能下降超过阈值时自动告警。
崩塌场景 2：用户缺乏专业知识
一个初级 DBA 看到某个查询执行了顺序扫描，就手动编写建议强制使用索引扫描。但可能因为索引选择性差，强制使用索引反而导致大量随机 I/O，性能更差。
应对策略：建立严格的变更流程。任何计划建议的修改必须经过性能测试验证，并由资深 DBA 审批。模块本身也提供了警告： “terrible outcomes are possible” 。
崩塌场景 3：查询结构变化
如果查询的 SQL 文本发生变化（例如添加了新条件），原来的 queryid 会改变，或者建议可能部分或完全不适用。目前 pg_stash_advice 依赖查询 ID 匹配，如果查询变了，建议可能被忽略或导致错误。
应对策略：在应用开发过程中，如果修改了核心查询，必须同步更新对应的计划建议。建议将建议字符串作为代码的一部分纳入版本控制。
崩塌场景 4：超出当前支持范围
目前，计划建议主要覆盖扫描方式、连接方法、连接顺序、并行度等，但不支持聚合方法、排序顺序等决策。如果你的性能问题源于这些方面，pg_plan_advice 帮不上忙。
应对策略：这类问题可能需要通过索引优化、SQL 重写或调整 work_mem 等参数解决。
未来展望：从 contrib 到核心，从建议到自动优化
Robert Haas 在邮件列表中坦言，这个模块包含了大量基础设施（如关系标识符系统，能唯一且稳定地指代查询中的任何关系），这些设施未来可能进入核心，为其他扩展或核心功能服务。
长远看，我们可能会看到：
自动建议：基于历史执行统计，系统自动为“问题查询”生成并应用优化建议。 更丰富的决策覆盖：支持索引类型、聚合方法、CTE 优化等。 与外部监控集成：从 Prometheus、Zabbix 等监控系统触发计划锁定/解锁。
DBA 的行动指南
1. 识别候选查询
查找那些计划不稳定、执行时间波动大、对性能敏感的查询。pg_stat_statements 是你的好帮手：
SELECT queryid, query, stddev_time, mean_time
FROM pg_stat_statements
WHERE stddev_time / mean_time > 0.5-- 波动系数 > 50%
ORDERBY stddev_time DESC;
2. 在测试环境验证
对每个候选查询：
在测试环境生成建议 应用建议 用典型负载测试性能，确保锁定后的计划确实更优或至少不差
3. 纳入变更管理
将建议字符串存储为文本文件，与数据库迁移脚本一起纳入 Git。部署时，通过初始化脚本设置 pg_stash_advice。
4. 监控和复审
定期检查锁定的查询是否仍然高效。如果业务或数据发生重大变化，重新评估。
结语
PostgreSQL 19 的 pg_plan_advice 套件不是对优化器的“不信任投票”，而是对现实世界复杂性的务实回应。它承认了代价模型的局限，并给予 DBA 一把精确的手术刀，而不是笨重的锤子。
从今天起，PostgreSQL 的查询优化不再只是优化器的事——你也可以参与决策了。但请记住：权力越大，责任越大。