PostgreSQL中SELECT列表与子查询使用unnest()的执行计划差异

我有两个结果等价的PostgreSQL查询（子查询版本执行更快），使用的是PostgreSQL 14。想请教为何子查询版本能启用并行执行，而前者不行？

查询1 #

SELECT unnest(articles.tags) AS unnested_tags
FROM articles
WHERE articles.user_id = '81c96625-3761-4cdd-a7eb-c9d752c7ed12'
GROUP BY unnested_tags;

执行计划 #

HashAggregate  (cost=304859.08..305073.95 rows=17190 width=32) (actual time=340.426..340.570 rows=120 loops=1)
   Group Key: unnest(tags)
   Batches: 1  Memory Usage: 793kB
   ->  ProjectSet  (cost=0.56..302452.08 rows=962800 width=32) (actual time=6.938..212.755 rows=814335 loops=1)
         ->  Index Scan using ix_user_article on articles  (cost=0.56..296915.98 rows=96280 width=116) (actual time=0.021..88.745 rows=101759 loops=1)
               Index Cond: (user_id = '81c96625-3761-4cdd-a7eb-c9d752c7ed12'::uuid)
 Planning Time: 0.290 ms
 JIT:
   Functions: 9
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.607 ms, Inlining 0.000 ms, Optimization 0.443 ms, Emission 6.493 ms, Total 7.543 ms
 Execution Time: 341.468 ms

查询2 #

SELECT certain.unnested_tags
FROM  (
   SELECT unnest(articles.tags) AS unnested_tags
   FROM articles
   WHERE articles.user_id = '81c96625-3761-4cdd-a7eb-c9d752c7ed12'
   ) AS certain
GROUP BY certain.unnested_tags;

执行计划 #

Group  (cost=311705.74..311753.41 rows=200 width=32) (actual time=224.268..235.861 rows=120 loops=1)
   Group Key: (unnest(articles.tags))
   ->  Gather Merge  (cost=311705.74..311752.41 rows=400 width=32) (actual time=224.252..235.754 rows=318 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=310705.72..310706.22 rows=200 width=32) (actual time=193.068..193.077 rows=106 loops=3)
               Sort Key: (unnest(articles.tags))
               Sort Method: quicksort  Memory: 30kB
               Worker 0:  Sort Method: quicksort  Memory: 30kB
               Worker 1:  Sort Method: quicksort  Memory: 30kB
               ->  Partial HashAggregate  (cost=310696.07..310698.07 rows=200 width=32) (actual time=192.832..192.860 rows=106 loops=3)
                     Group Key: unnest(articles.tags)
                     Batches: 1  Memory Usage: 40kB
                     Worker 0:  Batches: 1  Memory Usage: 40kB
                     Worker 1:  Batches: 1  Memory Usage: 40kB
                     ->  ProjectSet  (cost=0.56..298661.07 rows=401170 width=32) (actual time=10.415..119.371 rows=271445 loops=3)
                           ->  Parallel Index Scan using ix_user_article on articles  (cost=0.56..296354.35 rows=40117 width=116) (actual time=0.038..47.808 rows=33920 loops=3)
                                 Index Cond: (user_id = '81c96625-3761-4cdd-a7eb-c9d752c7ed12'::uuid)
 Planning Time: 0.224 ms
 JIT:
   Functions: 29
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 3.358 ms, Inlining 0.000 ms, Optimization 1.723 ms, Emission 29.476 ms, Total 34.557 ms
 Execution Time: 236.990 ms

原因分析 #

这两个查询的核心差异在于PostgreSQL 14优化器对查询结构的并行执行支持逻辑：

1. 查询1的限制：
   查询1中，HashAggregate直接依赖ProjectSet（由unnest生成的行集合操作）。PostgreSQL 14的优化器在处理顶层的HashAggregate时，若输入是ProjectSet这类行生成器，无法识别出可以将并行执行逻辑下推到底层的数据扫描和行生成阶段，因此只能以单进程方式执行整个流程。

2. 查询2的并行触发逻辑：
   查询2通过子查询将数据扫描+unnest的操作独立出来，优化器会将这部分视为一个可并行化的子计划。它会启动多个worker进程，每个worker独立执行Parallel Index Scan扫描部分数据，再通过ProjectSet生成unnest结果，随后做Partial HashAggregate（局部聚合）。最后通过Gather Merge收集所有worker的局部聚合结果，完成全局的分组操作。

这种拆分让优化器能够识别到并行执行的可行性，将数据负载分摊到多个worker进程，从而大幅缩短执行时间。

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