PostgreSQL双层嵌套子查询更新语句性能优化咨询

需求说明 #

将Person表中1至300万条数据的A列，更新为对应id在Person_detail表中B值最大的那条记录的A值。

原实现SQL #

update person 
set A = subquery.A
from (
    select id , A
    from person_detail  pd 
    where B = ( select max( B ) from  person_detail pd2 where  pd2.id = pd.id )
) as subquery
where subquery.id = person.id;

执行性能问题及PostgreSQL执行计划 #

原方案执行性能极差，对应的执行计划如下：

"Update on person (cost=83690027.90..99774746.68 rows=1949368 width=465)"
"  ->  Merge Join  (cost=83690027.90..99774746.68 rows=1949368 width=465)"
"        Merge Cond: (subquery.id= person.id)"
"        ->  Subquery Scan on a  (cost=83688759.49..96359650.45 rows=1949368 width=58)"
"              Filter: (a.ranked_order = 1)"
"              ->  WindowAgg  (cost=83688759.49..91486230.85 rows=389873568 width=29)"
"                    ->  Sort  (cost=83688759.49..84663443.41 rows=389873568 width=21)"
"                          Sort Key: pd.id, pd.B DESC" 
"                          ->  Seq Scan on person_detail ad  (cost=0.00..23488027.68 rows=389873568 width=21)"
"        ->  Index Scan using person_id_A_B_C_D on person  (cost=0.43..3378840.19 rows=3313406 width=394)"

表结构信息 #

Table Person
    id PK
    A  

Table Person_detail
    id  PK
    A   PK
    B   PK
    C   

优化思路与建议 #

1. 用窗口函数替代关联子查询，减少重复计算 #

原SQL中每个id都会执行一次max(B)子查询，属于嵌套循环关联，数据量大时会重复扫描Person_detail表。改用窗口函数ROW_NUMBER()只需扫描一次表即可分组取最大值对应的A值：

WITH ranked_detail AS (
    SELECT 
        id, 
        A,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY B DESC) AS rn
    FROM person_detail
)
UPDATE person p
SET A = rd.A
FROM ranked_detail rd
WHERE p.id = rd.id 
  AND rd.rn = 1
  AND p.id BETWEEN 1 AND 3000000; -- 限定更新范围，避免全表扫描

2. 创建针对性索引，优化排序与查找 #

执行计划显示对Person_detail做了全表扫描+排序，成本极高。创建覆盖索引让数据库直接从索引获取数据，避免排序和回表：

CREATE INDEX idx_pd_id_b_a ON person_detail(id, B DESC, A);

该索引包含分组字段id、排序字段B DESC和目标字段A，窗口函数计算时可直接使用索引，无需额外操作。

3. 分批更新，避免大事务锁表 #

一次性更新300万条数据会导致长时间锁表，影响其他业务。按id分段批量更新，每次更新1万条示例：

DO $$
DECLARE
    batch_size INT := 10000;
    start_id INT := 1;
    end_id INT := 3000000;
    current_start INT;
BEGIN
    current_start := start_id;
    WHILE current_start <= end_id LOOP
        WITH ranked_detail AS (
            SELECT 
                id, 
                A,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY B DESC) AS rn
            FROM person_detail
            WHERE id BETWEEN current_start AND LEAST(current_start + batch_size - 1, end_id)
        )
        UPDATE person p
        SET A = rd.A
        FROM ranked_detail rd
        WHERE p.id = rd.id 
          AND rd.rn = 1
          AND p.id BETWEEN current_start AND LEAST(current_start + batch_size - 1, end_id);
        
        COMMIT; -- 每批提交释放锁
        current_start := current_start + batch_size;
    END LOOP;
END $$;

4. 预计算结果集，临时表存储 #

若Person_detail表数据量极大，可先将每个id对应的目标值存入临时表，再用临时表更新Person表：

-- 创建临时表存储预计算结果
CREATE TEMP TABLE temp_max_detail AS
SELECT 
    id, 
    MAX(A) FILTER (WHERE B = MAX(B) OVER (PARTITION BY id)) AS max_a
FROM person_detail
GROUP BY id;

-- 加索引加速关联
CREATE INDEX idx_temp_id ON temp_max_detail(id);

-- 执行更新
UPDATE person p
SET A = t.max_a
FROM temp_max_detail t
WHERE p.id = t.id
  AND p.id BETWEEN 1 AND 3000000;

-- 删除临时表
DROP TABLE temp_max_detail;

内容的提问来源于stack exchange，提问作者Diego Quirós