PostgreSQL大表SUM计算极慢求助：仓库库存回溯优化

哇，200万条记录用游标逐行循环计算+更新，这性能不崩才怪！咱们直接换掉这种低效的方式，用PostgreSQL的窗口函数来搞定，几分钟就能完成你10小时都没跑完的任务。

问题根源分析 #

你原来的代码犯了一个典型的性能错误：

• 每次循环都对全表执行SUM(b.quantity)，相当于每处理1条记录就要扫描200万条数据
• 200万次循环下来，总操作量是200万 × 200万 = 4×10¹²次，这完全是灾难级的时间复杂度（O(n²)），根本不可能在合理时间内完成

优化方案：用窗口函数批量计算+更新 #

PostgreSQL的窗口函数专门用来处理这种累计/聚合类的逐行计算，只需要一次全表扫描就能算出所有记录的累计库存，然后批量更新，时间复杂度是O(n)，效率提升几个数量级。

完整优化代码 #

WITH running_stock_calc AS (
    SELECT
        mov_id,
        -- 按时间顺序（时间相同时用mov_id确保顺序唯一）计算累计库存
        SUM(quantity) OVER (ORDER BY timestamp, mov_id) AS current_stock_val
    FROM movements
)
UPDATE movements m
SET current_stock = rsc.current_stock_val
FROM running_stock_calc rsc
WHERE m.mov_id = rsc.mov_id;

针对指定日期的场景 #

如果你只需要更新某个指定日期（比如2020-10-03 15:24:13）之前的记录，只需在CTE里加过滤条件：

WITH running_stock_calc AS (
    SELECT
        mov_id,
        SUM(quantity) OVER (ORDER BY timestamp, mov_id) AS current_stock_val
    FROM movements
    WHERE timestamp <= '2020-10-03 15:24:13'
)
UPDATE movements m
SET current_stock = rsc.current_stock_val
FROM running_stock_calc rsc
WHERE m.mov_id = rsc.mov_id;

额外性能优化建议 #

1. 给timestamp字段加索引：如果timestamp还没有索引，临时建一个可以加速窗口函数的排序过程：

   CREATE INDEX idx_movements_timestamp ON movements(timestamp, mov_id);
   

   更新完成后如果不需要可以删掉：DROP INDEX idx_movements_timestamp;

2. 关闭自动提交（可选）：批量更新时，关闭自动提交可以减少事务日志的写入开销，提升速度：

   BEGIN;
   -- 执行上面的更新语句
   COMMIT;
   

为什么这个方案可行？ #

窗口函数是数据库引擎深度优化过的特性，它会用高效的排序和累加算法（比如归并排序+线性累加），只需要扫描一次表就能计算出所有行的累计值，然后通过mov_id关联批量更新，整个过程在PostgreSQL中处理200万条记录只需要几分钟甚至更短时间。

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