Postgres递归查询未用嵌套循环却用Merge Join致性能问题求助

执行简单递归查询时出现糟糕的执行计划，已执行Vacuum和Analyze操作，plm.link_plm表统计信息正常，该表共69568347行。测试显示：开启enable_hashjoin和enable_mergejoin时，递归查询采用Merge Join，耗时超1小时；关闭这两个参数后，使用基于索引的嵌套循环查询仅耗时0.1s。请问为何会选择Merge Join而非嵌套循环？

相关操作与代码 #

统计信息更新与表大小查询 #

• 更新表统计信息：

vacuum analyze plm.link_plm;

• 查询表总行数：

select count(*) from plm.link_plm;
-- 执行耗时12秒，返回69568347行

开启Hash Join和Merge Join的测试 #

测试代码：

set enable_hashjoin to on;
set enable_mergejoin to on;

with recursive r as (
    select l.* from plm.link_plm l where l.ci = 14546722
    union all select l.* from r join plm.link_plm l on l.ci = r.pi
) select distinct * from r;
-- 使用Merge Join，耗时超1小时

对应的执行计划：

HashAggregate  (cost=0.00..0.00 rows=0 width=0)
    Group Key: r.pi, r.ci, r.name, r.type, r.matrix, r.wipfirst, r.relfirst, r.partition, r.j
  CTE r
      ->  Recursive Union  (cost=0.00..0.00 rows=0 width=0)
            ->  Bitmap Heap Scan on link_plm l  (cost=0.00..0.00 rows=0 width=0)
                    Recheck Cond: (ci = 14546722)
                  ->  Bitmap Index Scan on link_plm_ci_pi_idx  (cost=0.00..0.00 rows=0 width=0)
                          Index Cond: (ci = 14546722)
            ->  Merge Join  (cost=0.00..0.00 rows=0 width=0)
                  ->  Sort  (cost=0.00..0.00 rows=0 width=0)
                          Sort Key: r_1.pi
                        ->  WorkTable Scan on r r_1  (cost=0.00..0.00 rows=0 width=0)
                  ->  Materialize  (cost=0.00..0.00 rows=0 width=0)
                        ->  Sort  (cost=0.00..0.00 rows=0 width=0)
                                Sort Key: l_1.ci
                              ->  Seq Scan on link_plm l_1  (cost=0.00..0.00 rows=0 width=0)
  ->  CTE Scan on r r  (cost=0.00..0.00 rows=0 width=0)

关闭Hash Join和Merge Join的测试 #

测试代码：

set enable_hashjoin to off;
set enable_mergejoin to off;

with recursive r as (
    select l.* from plm.link_plm l where l.ci = 14546722
    union all select l.* from r join plm.link_plm l on l.ci = r.pi
) select distinct * from r;
-- 使用基于索引的嵌套循环查询，耗时仅0.1秒

原因分析 #

PostgreSQL查询优化器选择Merge Join而非嵌套循环，核心原因是对递归CTE的结果规模预估严重偏离实际：

1. 递归结果集预估错误：优化器无法精准预判递归CTE每次迭代返回的行数，默认假设递归会产生大量数据。对于大数据量场景，优化器认为Merge Join成本更低——嵌套循环在驱动表数据量大时，会触发大量索引扫描，成本被高估；而Merge Join只需对两边数据各排序一次，后续关联成本低。但实际你的递归查询返回结果极少（0.1秒耗时足以证明），导致Merge Join需要对全表6900多万行排序的成本完全得不偿失。
2. 统计信息的局限性：即使执行了VACUUM ANALYZE，统计信息仅能反映全表数据分布，无法预判特定起始条件（ci=14546722）下的递归深度和结果规模。优化器只能基于通用统计做假设，而这个假设与实际情况不符。
3. 成本计算异常：你的执行计划中所有成本值均为0.00，这说明优化器在处理递归CTE时无法准确计算成本，或统计信息在递归场景下存在计算局限。这种情况下，优化器会 fallback 到默认适合大数据量的Merge Join策略。

简言之，优化器误判了递归查询的结果规模，选了适合大数据量的Merge Join，而实际场景下嵌套循环才是最优选择。

内容的提问来源于stack exchange，提问作者D. Hard