如何让批量UPDATE中的子查询启用Parallel Seq Scan并行扫描

问题背景 #

单独执行包含GROUP BY的SELECT查询时，PostgreSQL可以通过Parallel Seq Scan并行扫描并利用多个工作进程；但将该查询作为UPDATE的子查询时，会退化为串行Seq Scan，性能大幅下降。若改用FOR循环或游标处理，又会变成单事务提交，性能进一步恶化。

场景涉及百万级数据的查询与更新，子查询中的GROUP BY逻辑无法直接在UPDATE语句中关联实现，示例SQL如下：

UPDATE user 
SET user_type = select_user_type
FROM (
  SELECT id as select_id, array_agg(distinct(user_type)) as select_user_type 
  FROM txn join meta using(id) 
  GROUP BY id
) AS sub
WHERE sub.select_id = user.id;

当前临时解决方案为将SELECT结果导出为CSV后导入临时表再执行批量更新，但该方案耗时较长。核心诉求是让DML（UPDATE/INSERT/DELETE）中的GROUP BY子查询能够启用Parallel Seq Scan并行扫描。

原因分析 #

PostgreSQL的并行查询框架在处理DML语句的FROM子查询时，存在以下限制：

• UPDATE属于数据修改类操作，主进程需要同步子查询的执行结果来完成数据更新，而并行执行的子查询结果合并逻辑在DML上下文默认不会被触发。
• 当子查询包含聚合操作（如GROUP BY、array_agg）时，优化器会认为并行执行的协调成本高于收益，因此自动退选串行扫描，避免主进程与并行工作进程之间的复杂同步逻辑。

优化方案 #

1. 调整并行相关配置参数 #

通过修改PostgreSQL的配置参数，降低优化器选择并行扫描的阈值：

• 设置max_parallel_workers_per_gather：增大并行工作进程数上限，示例：

  SET max_parallel_workers_per_gather = 4; -- 会话级别生效，全局修改需在postgresql.conf中设置后重启
  

• 降低parallel_setup_cost和parallel_tuple_cost：减少并行启动和单元组处理的成本估算，让优化器更倾向于选择并行扫描：

  SET parallel_setup_cost = 1000;
  SET parallel_tuple_cost = 0.1;
  

2. 用CTE结合并行提示（PostgreSQL 12+） #

在CTE中显式添加PARALLEL提示，引导优化器启用并行扫描：

UPDATE user 
SET user_type = select_user_type
FROM (
  WITH parallel_cte AS (
    SELECT id as select_id, array_agg(distinct(user_type)) as select_user_type 
    FROM txn join meta using(id) 
    GROUP BY id
  )
  SELECT * FROM parallel_cte PARALLEL 4 -- 指定并行工作进程数
) AS sub
WHERE sub.select_id = user.id;

注：该提示仅为优化器提供参考，最终是否启用并行仍由优化器根据实际执行成本判断。

3. 数据库内物化子查询到临时表（推荐） #

相比导出CSV，直接在数据库内创建临时表并利用并行扫描填充，效率更高：

-- 创建临时表并指定并行工作进程
CREATE TEMP TABLE temp_user_type 
WITH (parallel_workers = 4)
AS SELECT id as select_id, array_agg(distinct(user_type)) as select_user_type 
   FROM txn join meta using(id) 
   GROUP BY id;

-- 为临时表创建索引，提升UPDATE关联速度
CREATE INDEX idx_temp_user_type_id ON temp_user_type(select_id);

-- 执行批量更新
UPDATE user 
SET user_type = select_user_type
FROM temp_user_type
WHERE temp_user_type.select_id = user.id;

CREATE TABLE AS语句支持并行执行，因此子查询部分可以启用Parallel Seq Scan，后续UPDATE关联临时表的性能也更优。

4. 更新表统计信息 #

确保优化器拥有准确的表数据分布信息，以便正确判断是否启用并行扫描：

ANALYZE txn;
ANALYZE meta;

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