千万级大数据量父子表关联UPDATE查询优化及耗时预估咨询

以下方案可大幅降低更新耗时，正常场景下几小时内即可完成全量更新：

• 提前配置必要索引
  确保event表的id为主键（自带唯一B树索引，缺失请先创建），同时给parameter表的eventId字段创建普通索引，语句如下：

  CREATE INDEX CONCURRENTLY idx_parameter_eventid ON parameter("eventId");
  

  用CONCURRENTLY参数建索引不会锁表，不影响线上业务正常运行。
• 拆分全量更新为分批小事务更新
  单事务更新1100万行数据会生成超大WAL日志，占用极高IO、内存资源，还会长时间锁表。建议按parameter表的主键范围拆分批次，每次更新1~10万行数据，示例脚本如下：

  DO $$
  DECLARE
      batch_size INT := 10000; -- 可根据服务器性能调整批次大小
      max_id INT;
      current_id INT := 0;
  BEGIN
      SELECT MAX(id) INTO max_id FROM parameter;
      WHILE current_id <= max_id LOOP
          UPDATE parameter p
          SET "order" = e."order"
          FROM event e
          WHERE p.id BETWEEN current_id AND current_id + batch_size - 1
          AND p."eventId" = e.id;
          COMMIT; -- 每批次提交一次事务，释放资源
          current_id := current_id + batch_size;
          -- 若为线上运行，可添加短延迟避免占满IO：PERFORM pg_sleep(0.1);
      END LOOP;
  END $$;
  

• 维护窗口可选优化
  如果是在业务低峰的维护窗口操作，可以先删除parameter表上的非必要索引、触发器（尤其是涉及order字段的索引），全量更新完成后再重建，速度可提升3~5倍。同时可以临时调大当前会话的内存参数：

  SET maintenance_work_mem = '4GB'; -- 按服务器实际可用内存调整
  

  提升Hash Join的运行效率。

• EXPLAIN输出的cost是基于数据库配置的抽象权重值（和seq_page_cost、cpu_tuple_cost等参数相关），无法直接转换为固定的毫秒值，不同硬件、业务负载下的对应关系差异极大，不建议用cost做正式的耗时预估。
• 最准确的预估方式是小范围实测：手动更新10万行数据，记录实际耗时，再按总数据量等比放大即可，比如10万行更新耗时30秒，1100万行的总耗时大概是 1100/10 * 30 = 3300秒 约55分钟，该结果和实际耗时偏差一般在20%以内，可以用来同步团队预期。

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