PostgreSQL用LAG()更新20万行营收表效率低，如何优化？

核心性能问题 #

现有语句的最大问题是UPDATE阶段没有指定主表和临时表tmp的关联条件，会触发全表笛卡尔积关联，相当于执行20万 * 20万 = 400亿次匹配，这是耗时异常高的首要原因。

优化措施 #

• 补全UPDATE的关联匹配条件，避免笛卡尔积
• 建立覆盖索引，让窗口函数计算时直接从索引取数，无需回表查询
• 可选添加过滤条件，仅更新未计算过增长率的行，避免重复更新

优化后完整代码 #

-- 第一步：创建覆盖索引（仅需执行一次，后续更新可复用）
-- PostgreSQL 版本
CREATE INDEX IF NOT EXISTS idx_ticker_time_revenue ON companies_analytics (ticker, time) INCLUDE (revenue);
-- MySQL 版本（不支持INCLUDE语法，直接把revenue加入联合索引即可）
-- CREATE INDEX IF NOT EXISTS idx_ticker_time_revenue ON companies_analytics (ticker, time, revenue);

-- 第二步：执行更新
WITH tmp AS (   
    SELECT
       ticker,
       time,
       revenue,
       LAG(revenue, 3) OVER w AS revenue_3
    FROM companies_analytics
    WINDOW w AS (
       PARTITION BY ticker ORDER BY time
    )
) 
UPDATE companies_analytics
SET revenue_growth_3y = tmp.revenue / NULLIF(tmp.revenue_3, 0)
FROM tmp
-- 补全关联条件，是性能提升的核心
WHERE companies_analytics.ticker = tmp.ticker 
  AND companies_analytics.time = tmp.time
-- 可选：增量更新时添加，跳过已计算过的行
-- AND companies_analytics.revenue_growth_3y IS NULL
;

效果说明 #

20万行数据场景下，优化后的语句执行耗时通常在10秒以内，远低于原语句的执行耗时。如果是首次全量更新，去掉可选的过滤条件即可。

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