为何PostgreSQL查询规划器无法转换相关子查询？

最近在研究PostgreSQL处理1+n查询的场景时，我发现一个实用的改写技巧：把返回聚合结果的相关子查询改写成左连接+分组聚合，不仅结果完全一致，性能还能获得明显提升。

原相关子查询写法 #

select film_id, title, 
  ( select array_agg(first_name) 
    from actor inner join film_actor using(actor_id) 
    where film_actor.film_id = film.film_id 
  ) as actors 
from film 
order by title;

改写为左连接+分组聚合 #

select f.film_id, f.title, array_agg(a.first_name) 
from film f 
left join film_actor fa using(film_id) 
left join actor a using(actor_id) 
group by f.film_id 
order by f.title;

性能对比结果 #

我做了针对性的性能测试：连续执行2轮各100次原查询，再执行2轮各100次改写后的查询，忽略第一轮作为预热环节。最终结果差异很直观：

• 100次原查询总耗时16秒
• 100次改写查询总耗时11秒

从执行计划也能看到底层逻辑的差异：

原相关子查询的执行计划

Index Scan using idx_title on film (cost=0.28..24949.50 rows=1000 width=51) (actual time=0.690..74.828 rows=1000 loops=1)
  SubPlan 1
    -> Aggregate (cost=24.84..24.85 rows=1 width=32) (actual time=0.068..0.068 rows=1 loops=1000)
          -> Hash Join (cost=10.82..24.82 rows=5 width=6) (actual time=0.034..0.055 rows=5 loops=1000)
                Hash Cond: (film_actor.actor_id = actor.actor_id)
                -> Bitmap Heap Scan on film_actor (cost=4.32..18.26 rows=5 width=2) (actual time=0.025..0.040 rows=5 loops=1000)
                      Recheck Cond: (film_id = film.film_id)
                      Heap Blocks: exact=5075
                      -> Bitmap Index Scan on idx_fk_film_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.015..0.015 rows=5 loops=1000)
                            Index Cond: (film_id = film.film_id)
                -> Hash (cost=4.00..4.00 rows=200 width=10) (actual time=0.338..0.338 rows=200 loops=1)
                      Buckets: 1024  Batches: 1  Memory Usage: 17kB
                      -> Seq Scan on actor (cost=0.00..4.00 rows=200 width=10) (actual time=0.021..0.133 rows=200 loops=1)
Planning time: 1.277 ms
Execution time: 75.525 ms

改写后连接查询的执行计划

Sort (cost=748.60..751.10 rows=1000 width=51) (actual time=35.865..36.060 rows=1000 loops=1)
  Sort Key: f.title
  Sort Method: quicksort  Memory: 199kB
  -> GroupAggregate (cost=645.31..698.78 rows=1000 width=51) (actual time=23.953..34.204 rows=1000 loops=1)
        Group Key: f.film_id
        -> Sort (cost=645.31..658.97 rows=5462 width=25) (actual time=23.910..25.210 rows=5465 loops=1)
              Sort Key: f.film_id
              Sort Method: quicksort  Memory: 619kB
              -> Hash Left Join (cost=84.00..306.25 rows=5462 width=25) (actual time=2.098..16.237 rows=5465 loops=1)
                    Hash Cond: (fa.actor_id = a.actor_id)
                    -> Hash Right Join (cost=77.50..231.03 rows=5462 width=21) (actual time=1.786..10.636 rows=5465 loops=1)
                          Hash Cond: (fa.film_id = f.film_id)
                          -> Seq Scan on film_actor fa (cost=0.00..84.62 rows=5462 width=4) (actual time=0.018..2.221 rows=5462 loops=1)
                          -> Hash (cost=65.00..65.00 rows=1000 width=19) (actual time=1.753..1.753 rows=1000 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 59kB
                                -> Seq Scan on film f (cost=0.00..65.00 rows=1000 width=19) (actual time=0.029..0.819 rows=1000 loops=1)
                    -> Hash (cost=4.00..4.00 rows=200 width=10) (actual time=0.286..0.286 rows=200 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 17kB
                          -> Seq Scan on actor a (cost=0.00..4.00 rows=200 width=10) (actual time=0.016..0.114 rows=200 loops=1)
Planning time: 1.648 ms
Execution time: 36.599 ms

为什么查询规划器无法自动完成这种转换？ #

这确实是个值得深究的问题，虽然这个例子里两种写法语义完全等价，但PostgreSQL的查询优化器不会贸然执行这类转换，核心原因有几个：

1. 语义等价性的严格校验成本
   不是所有相关子查询都能安全转成连接，比如子查询包含LIMIT、非确定性函数（如now()）或复杂过滤逻辑时，转换后可能改变结果。优化器需要确保100%语义等价才会转换，而这种校验在复杂场景下成本极高，甚至无法完成。

2. 优化器的启发式设计限制
   PostgreSQL优化器依赖启发式规则平衡优化效果和规划时间。这类子查询转连接的等价重写，默认规则里可能没覆盖这种特定的聚合子查询场景——毕竟要考虑的边界情况太多，过度优化反而可能引入bug。

3. 统计信息的不确定性
   优化器选择执行计划严重依赖表的统计信息。如果统计信息不准确，它无法判断"先连接再聚合"是否比"逐个查询子聚合"更高效，因此不会主动做转换。

4. 错误转换的风险规避
   对数据库来说，返回正确结果比性能更重要。哪怕99%的场景转换安全，只要存在1%的场景会导致结果错误，优化器就不会默认开启这种转换——性能差可以手动优化，但结果错误是致命的。

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