为何CTE内连接数达阈值后，查询优化器选择低效执行计划？

我有如下SQL查询：

EXPLAIN ANALYZE
WITH up AS (
  SELECT
    ua1.id ua1_id,
    gp1.id gp1_id,
    upai1.id upai1_id,
    upbi1.id upbi1_id,
    upiv1.id upiv1_id,
    vi.id vi_id,
    c.id c_id,
    sua.id sua_id
  FROM ua1
  LEFT JOIN gp1 ON
    ua1.gp1_id = gp1.id
  LEFT JOIN upai1 ON
    upai1.id = ua1.upai1_id
  LEFT JOIN upbi1 ON
    upbi1.id = ua1.upbi1_id
  LEFT JOIN upiv1 ON
    upiv1.id = ua1.upiv1_id
  LEFT JOIN vi ON
    vi.id = ua1.vi_id
  LEFT JOIN c ON
    c.id = vi.c_id
  LEFT JOIN sua ON
    sua.ua_id = ua1.id  
)
SELECT
  up1.*,
  hrm1.ua_id,
  hrm1.hr_id
FROM hrm1 hrm1
INNER JOIN up up1 ON
  up1.ua1_id = hrm1.ua_id
WHERE
hrm1.hr_id = 1

对应的低效执行计划（Hash Join+Gather）：

Gather  (cost=42072.86..122816.40 rows=6 width=40) (actual time=7.207..10.132 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
   ->  Hash Join  (cost=41072.86..121815.80 rows=2 width=40) (actual time=0.068..0.070 rows=0 loops=3)
Hash Cond: (ua1.id = hrm1.ua_id)
         ->  Parallel Hash Left Join  (cost=41045.41..121231.60 rows=212096 width=32) (never executed)
Hash Cond: (ua1.id = sua.ua_id)
               ->  Hash Left Join  (cost=36200.82..115830.26 rows=212096 width=28) (never executed)
                    Hash Cond: (vi.c_id = c.id)
                     ->  Parallel Hash Left Join  (cost=36192.93..115254.76 rows=212096 width=28) (never executed)
Hash Cond: (ua1.vi_id = vi.id)
                           ->  Hash Left Join  (cost=11544.90..85850.97 rows=212096 width=24) (never executed)
Hash Cond: (ua1.upiv1_id = upiv1.id)
                                 ->  Hash Left Join  (cost=11506.32..85255.64 rows=212096 width=24) (never executed)
Hash Cond: (ua1.upbi1_id = upbi1.id)
                                       ->  Parallel Hash Left Join  (cost=11216.81..84409.38 rows=212096 width=24) (never executed)
Hash Cond: (ua1.upai1_id = upai1.id)
                                             ->  Hash Left Join  (cost=677.14..70177.96 rows=212096 width=24) (never executed)
Hash Cond: (ua1.location_gp1_id = gp1.id)
                                                   ->  Parallel Seq Scan on ua ua1  (cost=0.00..68943.96 rows=212096 width=24) (never executed)
                                                   ->  Hash  (cost=478.69..478.69 rows=15876 width=4) (never executed)
                                                         ->  Index Only Scan using gp1_pkey on gp1 gp1  (cost=0.29..478.69 rows=15876 width=4) (never executed)
Heap Fetches: 0
                                             ->  Parallel Hash  (cost=7815.85..7815.85 rows=165985 width=4) (never executed)
                                                   ->  Parallel Seq Scan on upai1 upai1  (cost=0.00..7815.85 rows=165985 width=4) (never executed)
                                       ->  Hash  (cost=170.34..170.34 rows=9534 width=4) (never executed)
                                             ->  Seq Scan on upbi1 upbi1  (cost=0.00..170.34 rows=9534 width=4) (never executed)
                                 ->  Hash  (cost=22.70..22.70 rows=1270 width=4) (never executed)
                                       ->  Seq Scan on upiv1 upiv1  (cost=0.00..22.70 rows=1270 width=4) (never executed)
                           ->  Parallel Hash  (cost=17455.57..17455.57 rows=438357 width=8) (never executed)
                                 ->  Parallel Seq Scan on vi vi  (cost=0.00..17455.57 rows=438357 width=8) (never executed)
                     ->  Hash  (cost=5.17..5.17 rows=217 width=4) (never executed)
                           ->  Seq Scan on c c  (cost=0.00..5.17 rows=217 width=4) (never executed)
               ->  Parallel Hash  (cost=4679.82..4679.82 rows=13182 width=8) (never executed)
                     ->  Parallel Seq Scan on sua sua  (cost=0.00..4679.82 rows=13182 width=8) (never executed)
         ->  Hash  (cost=27.37..27.37 rows=6 width=8) (actual time=0.021..0.021 rows=0 loops=3)
Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Index Scan using hrm1_hr_id_idx on hrm1 hrm1  (cost=0.42..27.37 rows=6 width=8) (actual time=0.020..0.020 rows=0 loops=3)
Index Cond: (hr_id = 1)
Planning Time: 5.125 ms
Execution Time: 10.223 ms

但移除CTE内任意一个连接，或直接去掉CTE改用普通SELECT时，优化器会选择高效的Nested Loop计划，执行时间大幅降低（实际场景中差距可达毫秒级与秒级），对应的执行计划：

Nested Loop Left Join  (cost=2.83..91.77 rows=6 width=36) (actual time=0.016..0.017 rows=0 loops=1)
   ->  Nested Loop Left Join  (cost=2.42..88.95 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
         ->  Nested Loop Left Join  (cost=1.99..85.80 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
               ->  Nested Loop Left Join  (cost=1.83..84.76 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
                     ->  Nested Loop Left Join  (cost=1.55..82.95 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
                           ->  Nested Loop Left Join  (cost=1.13..79.83 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
                                 ->  Nested Loop  (cost=0.84..78.01 rows=6 width=32) (actual time=0.015..0.016 rows=0 loops=1)
                                       ->  Index Scan using hrm1_hr_id_idx on hrm1 hrm1  (cost=0.42..27.37 rows=6 width=8) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: (hr_id = 6766566)
                                       ->  Index Scan using ua_pkey on ua ua1  (cost=0.42..8.44 rows=1 width=24) (never executed)
Index Cond: (id = hrm1.ua_id)
                                 ->  Index Only Scan using gp1_pkey on gp1 gp1  (cost=0.29..0.30 rows=1 width=4) (never executed)
Index Cond: (id = ua1.location_gp1_id)
Heap Fetches: 0
                           ->  Index Only Scan using upai1_pkey on upai1 upai1  (cost=0.42..0.52 rows=1 width=4) (never executed)
Index Cond: (id = ua1.upai1_id)
Heap Fetches: 0
                     ->  Index Only Scan using upbi1_pkey on upbi1 upbi1  (cost=0.29..0.30 rows=1 width=4) (never executed)
Index Cond: (id = ua1.upbi1_id)
Heap Fetches: 0
               ->  Index Only Scan using upiv1_pkey on upiv1 upiv1  (cost=0.15..0.17 rows=1 width=4) (never executed)
Index Cond: (id = ua1.upiv1_id)
Heap Fetches: 0
         ->  Index Only Scan using vi_id_pkey on vi vi  (cost=0.43..0.52 rows=1 width=4) (never executed)
Index Cond: (id = ua1.vi_id)
Heap Fetches: 0
   ->  Index Scan using sua_ua_id_uniq_idx on sua sua  (cost=0.41..0.47 rows=1 width=8) (never executed)
Index Cond: (ua_id = ua1.id)
Planning Time: 7.002 ms
Execution Time: 0.089 ms

我想了解：

1. 为什么增加CTE内的连接后，优化器会选择低效的执行计划？
2. 有哪些可行措施能避免这类问题在查询迭代扩展时再次出现？

1. CTE的优化屏障限制 #

PostgreSQL中默认CTE是优化屏障：优化器会单独计算CTE的执行计划，再将其作为临时表与外部表连接。原本高效的路径是先通过hrm1.hr_id=1过滤出少量数据（仅6行），再通过ua_id关联后续表，但CTE强制先计算ua1关联所有左连接表的完整结果集（预估21万行），优化器只能选择Hash Join处理大结果集与小hrm1子集的连接，无法将外部过滤条件下推到CTE内部。

2. 统计信息与成本估算偏差 #

当CTE内连接的表数量增加时，优化器对结果集行数、成本的估算误差会被放大。它可能错误认为CTE结果集规模适合Hash Join，忽略了外部过滤条件能大幅减少实际处理的数据量。尤其左连接较多时，优化器难以准确预估每个连接后的行数，导致选择了不适合小数据集的Hash Join策略。

• 移除CTE，改用内联子查询：将CTE逻辑直接合并到主查询中，让优化器全局评估执行路径，把hrm1的过滤条件下推到所有关联表查询中，自然会选择Nested Loop这类适合小数据集的连接方式。
• 启用CTE内联优化：PostgreSQL 12+版本可设置enable_cte_inline = on，让优化器自动将简单CTE内联到主查询，打破优化屏障。复杂CTE可能仍会被当作临时表，需结合场景测试。
• 手动引导连接策略：临时设置SET enable_hashjoin = off（不建议全局开启）强制优化器选择Nested Loop，或通过查询结构调整引导优化器，但这种方式灵活性差，适配有限。
• 更新统计信息：定期执行ANALYZE更新表统计信息，让优化器更准确预估结果集规模和连接成本，减少估算偏差导致的错误计划。
• 重构查询逻辑：将过滤条件尽可能前置，比如先查询hrm1的结果，再以此为基础关联ua1及其他表，避免提前生成大结果集。

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