PostgreSQL累计更新语句性能优化咨询：750k行数据表提速方法

问题背景 #

我有一张数据表，结构如下：

id (int) 
col1 (int) 
col2 (varchar) 
date1 (date) 
col3 (int) 
cumulative_col3 (int) 

表内约有75万行数据。需要将cumulative_col3字段更新为相同col1、col2分组下，date1小于等于当前行date1的col3累计值。

已创建索引：(date1)、(date1, col1, col2)和(col1, col2)。尝试了以下查询，但执行耗时过长：

update table_name
set cumulative_col3 = (select sum(s.col3)
                       from table_name s
                       where s.date1 <= table_name.date1
                         and s.col1 = table_name.col1
                         and s.col2 = table_name.col2);

方案1：使用窗口函数生成累计值后批量更新 #

原查询的问题在于逐行执行子查询聚合，相当于75万次独立的SUM计算，效率极低。改用窗口函数可以单次扫描表完成所有累计值计算，再批量更新：

WITH cumulative_data AS (
    SELECT 
        id,
        SUM(col3) OVER (PARTITION BY col1, col2 ORDER BY date1) AS new_cumulative
    FROM table_name
)
UPDATE table_name t
SET cumulative_col3 = cd.new_cumulative
FROM cumulative_data cd
WHERE t.id = cd.id;

• PARTITION BY col1, col2按指定字段分组，ORDER BY date1确保累计值按日期顺序计算
• 窗口函数的计算逻辑是流式处理，比逐行子查询效率提升数倍
• 确保id是主键或唯一索引，关联更新时能快速匹配行

方案2：兼容旧版数据库的临时表方案 #

如果数据库不支持CTE（如旧版MySQL），可以先将累计结果存入临时表，再执行更新：

-- 创建临时表存储累计值
CREATE TEMPORARY TABLE temp_cumulative AS
SELECT 
    id,
    SUM(col3) OVER (PARTITION BY col1, col2 ORDER BY date1) AS new_cumulative
FROM table_name;

-- 关联临时表完成更新
UPDATE table_name t
JOIN temp_cumulative tc ON t.id = tc.id
SET t.cumulative_col3 = tc.new_cumulative;

-- 临时表会在会话结束后自动删除，也可手动清理
DROP TEMPORARY TABLE temp_cumulative;

方案3：优化索引适配计算逻辑 #

现有索引可能未完全匹配窗口函数的分区+排序逻辑，创建(col1, col2, date1)复合索引可以让数据库直接按顺序读取分组数据，避免额外排序开销：

CREATE INDEX idx_col1_col2_date1 ON table_name(col1, col2, date1);

• 该索引完美适配PARTITION BY col1, col2 ORDER BY date1的计算逻辑，能大幅加速窗口函数的执行

方案4：分批更新避免资源耗尽 #

如果数据库内存或锁资源有限，一次性更新75万行可能导致锁表或内存溢出，可以按id分批处理：

SET @batch_size = 10000;
SET @last_id = 0;

REPEAT
    WITH cumulative_batch AS (
        SELECT 
            id,
            SUM(col3) OVER (PARTITION BY col1, col2 ORDER BY date1) AS new_cumulative
        FROM table_name
        WHERE id > @last_id
        ORDER BY id
        LIMIT @batch_size
    )
    UPDATE table_name t
    JOIN cumulative_batch cb ON t.id = cb.id
    SET t.cumulative_col3 = cb.new_cumulative;
    
    SELECT MAX(id) INTO @last_id FROM cumulative_batch;
UNTIL @last_id IS NULL END REPEAT;

• 每次处理1万行，减少单次操作的资源占用，适合资源紧张的数据库环境

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