PostgreSQL同表更新关联前一天同分钟ID执行过慢如何优化

你当前的性能瓶颈核心是两点：

1. 已有索引candles_idx1(day,minute)不符合查询的左前缀匹配规则：你的子查询先匹配minute相等，再过滤day范围，现有索引首字段是day，无法被该查询命中，导致数据库只能走主键索引扫描后额外过滤day和minute条件，8万行逐行执行子查询的成本极高。
2. 逐行关联子查询的执行模式效率极低，每行数据都要触发一次子查询扫描。

方案1：通用场景（无特殊业务假设） #

第一步：新建适配查询的联合索引 #

该索引完全匹配你的查询逻辑，不需要回表即可直接拿到目标值：

CREATE INDEX idx_candles_minute_day_id ON candles (minute, day DESC, id);

索引逻辑说明：左前缀匹配等值条件minute，同minute下按day降序排序，末尾存储id值，查询时匹配到的第一条数据就是小于当前day的最大id，直接返回不需要额外扫描。

第二步：改用窗口函数批量计算更新 #

替代逐行子查询，仅需一次全表扫描即可计算出所有行的id_d1值：

WITH prev_calc AS (
    SELECT
        id,
        LAG(id) OVER (PARTITION BY minute ORDER BY day) AS prev_day_id
    FROM candles
)
UPDATE candles s
SET id_d1 = prev_calc.prev_day_id
FROM prev_calc
WHERE s.id = prev_calc.id;

方案2：业务可假设day连续、每个minute每天仅一条记录 #

如果你的day字段是连续的数值（比如按天自增的序号，或者yyyymmdd格式的日期编码且交易日连续无缺失），可以用等值关联实现最快更新：

-- 可复用方案1的索引，也可单独用(minute, day)索引
UPDATE candles s2
SET id_d1 = s.id
FROM candles s
WHERE s.minute = s2.minute
AND s.day = s2.day - 1;

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