PostgreSQL跨表更新千万级大表耗时超6小时的优化咨询

• 临时表上的两个索引完全无效，纯增加开销
  从执行计划可以看到，不管是临时表的全表顺序扫描，还是后续的Hash Join逻辑，都没有用到任何索引。你在临时表上建的两个索引没有任何实际价值：
  1. 单独建的column1 btree索引：Hash Join场景下需要把临时表的所有关联数据读出来建哈希，全表顺序扫描的速度远高于走索引的随机读，索引根本不会被触发使用。
  2. 包含所有非column1字段的多列联合索引：首先这个索引连关联用的column1字段都不包含，其次btree遵循最左匹配规则，就算要用来定位行，索引第一个列是column2，和关联条件完全不匹配，整个索引体积极大的情况下，读取代价比直接扫全表高好几倍，除了占存储、拉长临时表的生成时间，没有任何作用。
• 单事务更新2200万行是PostgreSQL的典型性能瓶颈
  PostgreSQL的UPDATE是“标记旧元组为死亡、写入全新元组”的逻辑，不是原地修改数据。一次性更新2200万行意味着要写入同等规模的死亡元组、产生数十GB的WAL日志，单事务体积过大会触发WAL堆积、锁持有时间过长、后续Vacuum压力陡增等问题，执行6小时跑不完是这类大表更新的常见结果。
• 全表Hash Join的IO量被严重放大
  从执行计划计算的行宽来看，单次Hash Join需要读入table_A全表2200万行（总大小约40GB）、临时表全表2200万行（总大小约50GB）才能完成哈希构建，仅读盘环节就需要近百GB的IO，还没算更新过程中写入新元组的开销，整体IO量本身就超出单条SQL的高效执行范围。
• 关联字段存在隐式类型转换
  执行计划的Hash Cond显示((tmp.column1)::text = (table_A.column1)::text)，说明两个表的column1字段类型不一致，关联时强制做了类型转换，既增加了计算开销，也会导致即便在表A的column1上建索引，也无法被正常命中。

• 第一时间删掉临时表上的所有无效索引
  直接执行DROP INDEX IF EXISTS idx_table_TMP, idx2_table_TMP;，Hash Join场景下全表顺序扫描临时表的速度比走索引快10倍以上，这些索引除了占用存储、拖慢临时表生成的速度，没有任何实际价值。
• 将单条大UPDATE拆分为分批小事务更新
  这是最核心的提速手段，不要一次性提交2200万行的更新事务，按5万行/批的粒度循环提交：

  -- 循环执行，每次更新5万行，直到处理完全部数据
  WITH batch AS (
    -- 按物理行号取固定批次的行，避免重复处理
    SELECT ctid 
    FROM table_A 
    WHERE ctid > :last_processed_ctid
    ORDER BY ctid
    LIMIT 50000
  )
  UPDATE table_A a
  SET
    table_A_column2 = tmp.column2,
    table_A_column3 = tmp.column3
    -- 其余29个待更新字段
  FROM batch b
  INNER JOIN table_TMP tmp ON a.column1 = tmp.column1
  WHERE a.ctid = b.ctid;
  

  小事务的WAL写入是顺序批量提交的，不会出现大事务下的IO抖动、锁持有过久的问题，整体执行速度通常比单条大UPDATE快3~5倍。
• 优化关联路径，替换全表Hash Join
  给table_A的column1建一个普通btree索引：CREATE INDEX CONCURRENTLY idx_a_column1 ON table_A(column1);，注意这里一定要给表A建索引，不是给临时表建。配合上面的分批更新逻辑，优化器会自动走Nested Loop关联：每次处理小批次的待更新行时，通过column1的索引快速定位临时表的匹配行，不需要读入整个table_A的全表数据建哈希，IO量可以下降两个数量级。建索引时注意对齐两个表column1的字段类型，避免隐式转换导致索引失效。
• 高IO场景下的替代方案（如果业务允许）
  如果不需要逐行锁更新，直接用批量建表的方式替换原表，速度会比UPDATE快5~10倍：

  -- 新建结构完全一致的新表，批量拼接所有字段
  CREATE TABLE table_A_new AS
  SELECT
    -- 保留table_A所有原有非更新字段
    a.id,
    a.create_time,
    -- 用临时表的字段覆盖待更新的31个字段，无匹配则保留原有值
    COALESCE(tmp.column2, a.table_A_column2) AS table_A_column2,
    COALESCE(tmp.column3, a.table_A_column3) AS table_A_column3
    -- 其余29个待更新字段
  FROM table_A a
  LEFT JOIN table_TMP tmp ON a.column1 = tmp.column1;
  -- 后续在新表上建索引、约束，完成后直接rename替换原表即可
  

  这种方式是纯顺序批量写，没有UPDATE产生的死元组开销，写放大远低于逐行更新。
• 更新前的必要准备
  临时表生成后执行ANALYZE table_TMP;更新统计信息，避免优化器因为统计信息不准选错执行计划；全量更新完成后执行一次VACUUM ANALYZE table_A;，清理更新产生的死元组、更新表的统计信息。

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