PostgreSQL13仅EXPLAIN ANALYZE触发并行普通查询单进程执行异常

API 探路者

2026-8-30

PostgreSQL 13 仅EXPLAIN ANALYZE触发并行、普通执行走单进程问题排查

环境信息

数据库版本为PostgreSQL 13，当前并行相关参数配置如下：

SELECT name, setting FROM pg_Settings WHERE name LIKE '%parallel%'

查询结果：

name                            |setting|
--------------------------------+-------+
enable_parallel_append          |on     |
enable_parallel_hash            |on     |
force_parallel_mode             |off    |
max_parallel_maintenance_workers|4      |
max_parallel_workers            |96     |
max_parallel_workers_per_gather |2      |
min_parallel_index_scan_size    |64     |
min_parallel_table_scan_size    |1024   |
parallel_leader_participation   |on     |
parallel_setup_cost             |1000   |
parallel_tuple_cost             |0.1    |

问题现象

执行带EXPLAIN ANALYZE的分组聚合查询时，并行查询正常生效，总耗时仅3秒，执行计划如下：

EXPLAIN (analyze) 
SELECT   t1_code
        ,COUNT(1)  AS cnt
FROM     t1 a
WHERE    1=1
GROUP BY t1_code

Finalize GroupAggregate  (cost=620185.13..620185.64 rows=2 width=12) (actual time=2953.797..3186.877 rows=2 loops=1)
      Group Key: t1_code
      ->  Gather Merge  (cost=620185.13..620185.60 rows=4 width=12) (actual time=2953.763..3186.835 rows=6 loops=1)
            Workers Planned: 2
            Workers Launched: 2
            ->  Sort  (cost=619185.11..619185.11 rows=2 width=12) (actual time=2926.805..2926.808 rows=2 loops=3)
                  Sort Key: t1_code
                  Sort Method: quicksort  Memory: 25kB
                  Worker 0:  Sort Method: quicksort  Memory: 25kB
                  Worker 1:  Sort Method: quicksort  Memory: 25kB
                  ->  Partial HashAggregate  (cost=619185.08..619185.10 rows=2 width=12) (actual time=2926.763..2926.768 rows=2 loops=3)
                        Group Key: t1_code
                        Batches: 1  Memory Usage: 24kB
                        Worker 0:  Batches: 1  Memory Usage: 24kB
                        Worker 1:  Batches: 1  Memory Usage: 24kB
                        ->  Parallel Seq Scan on t1 a  (cost=0.00..551015.72 rows=13633872 width=4) (actual time=0.017..1412.845 rows=10907098 loops=3)
    Planning Time: 1.295 ms
    JIT:
      Functions: 21
      Options: Inlining true, Optimization true, Expressions true, Deforming true
      Timing: Generation 2.595 ms, Inlining 156.371 ms, Optimization 112.165 ms, Emission 63.886 ms, Total 335.017 ms
    Execution Time: 3243.358 ms

去掉EXPLAIN ANALYZE直接执行相同查询时，查询未使用并行执行逻辑，查看pg_stat_activity视图可见仅1个进程运行，总耗时翻倍至6秒，其中t1表大小为3GB。

补充测试结果

额外测试带VERBOSE、BUFFERS选项的EXPLAIN ANALYZE查询，现象和之前一致：带ANALYZE选项时可正常触发并行，去掉ANALYZE直接执行查询仍走单进程，即使将force_parallel_mode参数设置为ON也没有效果，对应执行SQL与计划如下：

EXPLAIN(ANALYZE, VERBOSE, BUFFERS)
SELECT COUNT(*) 
FROM (
     SELECT 
           ID
      FROM T2
     WHERE CODE1 <> '003'
       AND CODE2     <> 'Y'
       AND CODE3      <> 'Y'
     GROUP BY ID  
     ) t1 ;

Aggregate  (cost=204350.48..204350.49 rows=1 width=8) (actual time=2229.919..2229.997 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=216 read=140248 dirtied=10
  I/O Timings: read=1404.532
  ->  Finalize HashAggregate  (cost=202326.98..203226.31 rows=89933 width=14) (actual time=2128.682..2199.811 rows=605244 loops=1)
        Output: T2.ID
        Group Key: T2.ID
        Batches: 1  Memory Usage: 53265kB
        Buffers: shared hit=216 read=140248 dirtied=10
        I/O Timings: read=1404.532
        ->  Gather  (cost=182991.39..201877.32 rows=179866 width=14) (actual time=1632.564..1817.564 rows=1019955 loops=1)
              Output: T2.ID
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=216 read=140248 dirtied=10
              I/O Timings: read=1404.532
              ->  Partial HashAggregate  (cost=181991.39..182890.72 rows=89933 width=14) (actual time=1592.762..1643.902 rows=339952 loops=3)
                    Output: T2.ID
                    Group Key: T2.ID
                    Batches: 1  Memory Usage: 32785kB
                    Buffers: shared hit=216 read=140248 dirtied=10
                    I/O Timings: read=1404.532
                    Worker 0:  actual time=1572.928..1624.075 rows=327133 loops=1
                      Batches: 1  Memory Usage: 28689kB
                      JIT:
                        Functions: 8
                        Options: Inlining false, Optimization false, Expressions true, Deforming true
                        Timing: Generation 1.203 ms, Inlining 0.000 ms, Optimization 0.683 ms, Emission 9.159 ms, Total 11.046 ms
                      Buffers: shared hit=72 read=43679 dirtied=2
                      I/O Timings: read=470.405
                    Worker 1:  actual time=1573.005..1619.235 rows=330930 loops=1
                      Batches: 1  Memory Usage: 28689kB
                      JIT:
                        Functions: 8
                        Options: Inlining false, Optimization false, Expressions true, Deforming true
                        Timing: Generation 1.207 ms, Inlining 0.000 ms, Optimization 0.673 ms, Emission 9.169 ms, Total 11.049 ms
                      Buffers: shared hit=63 read=44135 dirtied=6
                      I/O Timings: read=460.591
                    ->  Parallel Seq Scan on T2  (cost=0.00..176869.37 rows=2048806 width=14) (actual time=10.934..1166.528 rows=1638627 loops=3)
                          Filter: (((T2.CODE1)::text <> '003'::text) AND ((T2.CODE2)::text <> 'Y'::text) AND ((T2.CODE3)::text <> 'Y'::text))
                          Rows Removed by Filter: 24943
                          Buffers: shared hit=216 read=140248 dirtied=10
                          I/O Timings: read=1404.532
                          Worker 0:  actual time=10.083..1162.319 rows=1533436 loops=1
                            Buffers: shared hit=72 read=43679 dirtied=2
                            I/O Timings: read=470.405
                          Worker 1:  actual time=10.083..1161.430 rows=1561181 loops=1
                            Buffers: shared hit=63 read=44135 dirtied=6
                            I/O Timings: read=460.591
Planning:
  Buffers: shared hit=70
Planning Time: 0.253 ms
JIT:
  Functions: 31
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 4.451 ms, Inlining 0.000 ms, Optimization 2.182 ms, Emission 29.515 ms, Total 36.148 ms
Execution Time: 2234.037 ms

根因说明

核心问题出在执行普通查询的客户端模式：绝大多数数据库GUI工具（DBeaver、DataGrip、Navicat等）默认采用游标分页模式发送查询请求，实现结果集分批加载，避免一次性拉取大量数据撑爆客户端内存。而PostgreSQL对游标查询做了硬限制：不会生成并行执行计划。因为游标设计目标是支持按需拉取少量行，并行查询则需要一次性处理全量数据再汇总结果，二者逻辑冲突，该限制不受force_parallel_mode等并行参数影响，这也是强制开并行参数无效的原因。
EXPLAIN ANALYZE执行时不会通过游标返回业务数据，会完整跑完整个查询后统一输出执行统计信息，因此可以正常触发并行，和观察到的现象完全吻合。
当前的并行参数配置本身没有问题：3GB的T1表远大于min_parallel_table_scan_size对应的8MB阈值，max_parallel_workers_per_gather、max_parallel_workers等参数配置均满足并行执行要求。

验证方法

执行不带ANALYZE的普通EXPLAIN：

EXPLAIN SELECT t1_code,COUNT(1) AS cnt FROM t1 a GROUP BY t1_code;

如果输出计划中包含Gather、Parallel Seq Scan等并行节点，说明优化器本身可以生成正确的并行计划，问题完全出在客户端游标执行模式上。

使用psql命令行工具直接执行查询（psql默认不启用游标分页，除非手动设置FETCH_COUNT参数），会发现查询可以正常触发并行，耗时和EXPLAIN ANALYZE结果一致。

解决方案

方案1：调整GUI客户端配置，关闭游标分页/按页获取结果功能，将结果集fetch size设置为0，让客户端一次性拉取全量结果即可触发并行。常见工具配置路径：
- DBeaver：右键连接 -> 编辑连接 -> 结果集 -> 取消勾选「按页面读取结果」，将fetch size设为0
- DataGrip/IDEA系列：打开设置 -> 工具 -> 数据库 -> 常规 -> 勾选「同步执行时获取全部结果集」，将fetch size设为0
- Navicat：右键连接 -> 连接属性 -> 高级 -> 取消勾选「使用分页查询」
方案2：如果是业务代码中通过JDBC/ODBC等驱动连接数据库，将Statement的fetchSize参数设置为0，驱动会一次性拉取全量结果触发并行。注意如果查询结果集非常大，一次性拉取会占用较多客户端内存，需要提前评估内存承载能力。
方案3：如果业务场景必须使用游标分批拉取结果，无法关闭分页，没有参数可以强制游标场景下启用并行，建议通过添加合适索引、优化SQL逻辑的方式降低串行执行耗时。

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

PostgreSQL13仅EXPLAIN ANALYZE触发并行 普通查询单进程执行异常

环境信息 #

问题现象 #

补充测试结果 #

根因说明 #

验证方法 #

解决方案 #