PostgreSQL查询规划器/优化器：能否获取候选执行计划？

默认情况下，PostgreSQL的EXPLAIN ANALYZE只会展示最终选中的最优执行计划，不会保留那些被优化器评估后舍弃的候选计划。不过，我们有几种办法能获取到这些候选计划的相关信息，帮你分析优化器的决策过程：

1. 利用auto_explain扩展的计划统计功能 #

auto_explain是PostgreSQL自带的扩展，专门用来记录执行计划。其中log_planner_stats参数是关键——开启它后，优化器会把生成计划过程中考虑的候选路径、成本估算等细节都记录到日志里，包括那些被舍弃的选项。

配置步骤：

• 在postgresql.conf中添加/修改以下参数：

  shared_preload_libraries = 'auto_explain'  # 需要重启生效
  auto_explain.log_min_duration = 0          # 记录所有查询的计划
  auto_explain.log_planner_stats = on        # 开启计划器统计日志
  auto_explain.log_analyze = on              # 可选，同时记录执行统计
  

• 重启PostgreSQL或者执行SELECT pg_reload_conf();重载配置
• 执行你的查询后，查看数据库日志，就能看到优化器考虑过的各种扫描方式（比如SeqScan、IndexScan）、连接顺序、连接方法（Nested Loop、Hash Join等）的成本对比，以及为什么某些候选被舍弃。

2. 调整GEQO相关参数（针对复杂多表查询） #

当查询涉及的表数量超过geqo_threshold（默认是12）时，PostgreSQL会使用遗传算法（GEQO）来生成候选计划，而不是穷举所有可能。你可以通过调整以下参数让GEQO生成更多候选计划，并记录相关信息：

• 提高geqo_effort（默认是5）：值越高，GEQO生成的候选计划数量越多，能看到更多不同的连接顺序尝试
• 开启geqo_log（默认是off）：会把GEQO的迭代过程、候选计划的成本变化记录到日志里

同样，这些参数建议在测试环境调整，因为更高的geqo_effort会增加优化器的计算时间。

3. 用pg_hint_plan强制测试不同候选计划 #

虽然pg_hint_plan不能直接获取优化器的候选计划，但它允许你通过注释强制优化器选择特定的执行路径（比如强制全表扫描、指定连接顺序）。通过对比这些强制计划的成本和默认计划，你可以反向推断优化器原本考虑过哪些候选，以及为什么最终选择了最优解。

示例：

/*+ SeqScan(users) HashJoin(users orders) */
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

执行这个查询的EXPLAIN ANALYZE，就能看到强制选择的计划成本，和默认计划对比，就能明白优化器为什么没选这个路径。

4. 查看优化器的调试日志 #

如果你想深入了解优化器的决策细节，可以开启调试级别的日志：

• 设置log_min_messages = debug1和client_min_messages = debug1
• 执行EXPLAIN ANALYZE你的查询
• 查看数据库日志，里面会有优化器在生成计划时的每一步决策：比如“考虑对表t使用IndexScan，成本为X”“舍弃SeqScan，因为成本更高”等信息

不过这个方法的日志输出非常繁琐，需要你对PostgreSQL优化器的内部逻辑有一定了解才能解读。

内容的提问来源于stack exchange，提问作者Ymi