如何优化PostgreSQL动态分组统计更新查询的执行效率

我有两张表：table1（约100万条记录）、table2（约300万条数据）。之前使用以下PL/pgSQL代码更新table1的result字段，代码逻辑符合预期，但效率极低——仅更新100条记录就耗时超过1小时：

do $$ 
declare 
  rec record;
  current_joins text;
  current_result int;
begin
  for rec in select joins from table1 loop
     select rec.joins into current_joins;
     execute format('select count(*) 
                     from (
                        select 1
                        from table2 
                        group by %1$s 
                        having count(*)>=1 
                     ) as some_alias;',
                     current_joins) 
              into current_result;
     update table1 set result=current_result where joins=current_joins;
  end loop;
end $$;

需求：提供可实现相同逻辑的高效替代代码。表结构说明：table1包含joins（文本类型，存储用于分组的字段列表）和result（整数类型，存储统计结果）；table2为业务数据表，需根据joins指定的字段分组后统计分组数量。

优化方案：预计算批量更新 #

原代码的核心问题是逐行循环执行查询和更新，相当于对每条不同的joins值重复执行一次分组统计+更新操作，当数据量较大时，数据库的IO和事务开销会被无限放大。以下是优化后的代码：

-- 1. 预计算所有不同分组的统计结果，存入临时表
CREATE TEMP TABLE temp_group_counts AS
SELECT 
    distinct_joins.joins,
    -- 动态计算该分组下的统计数
    (SELECT COUNT(*) FROM (SELECT 1 FROM table2 GROUP BY distinct_joins.joins) AS sub) AS count_result
FROM (SELECT DISTINCT joins FROM table1) AS distinct_joins;

-- 2. 批量更新table1，一次完成所有记录的更新
UPDATE table1
SET result = temp_group_counts.count_result
FROM temp_group_counts
WHERE table1.joins = temp_group_counts.joins;

-- 可选：清理临时表
DROP TABLE temp_group_counts;

优化原理 #

1. 先一次性提取table1中所有唯一的joins值，避免重复计算相同分组的统计结果
2. 批量计算所有分组的统计数，仅执行一次全量分组统计逻辑
3. 通过FROM子句关联临时表，一次性完成table1的更新操作，将原有的百万次独立更新合并为一次批量更新

额外优化建议 #

• 如果table2中joins字段组合的查询频率高，可以针对这些字段组合创建复合索引，加速分组统计的速度
• 若数据库版本支持，可以考虑使用MATERIALIZED VIEW替代临时表，后续更新时只需刷新视图即可

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