嘿，这个场景我太熟了——700张表全表更新跑1小时确实有点离谱，咱们先解决你最关心的问题，再给你一套生产环境可用的优化方案：

核心疑问：加WHERE条件能不能减少执行时间？ #

绝对可以，而且效果可能非常明显！

原脚本的UPDATE语句不带任何过滤条件，会做几件低效的事：

• 对每张表执行全表扫描，不管deleted_flag当前是什么值
• 修改表中所有行的deleted_flag为false（哪怕它本来就是false）
• 生成大量不必要的WAL日志（Write-Ahead Log），增加磁盘IO压力
• 长时间持有表级锁，可能阻塞其他业务操作

而修改后的带WHERE deleted_flag=true的语句，只会处理真正需要修改的行：

• 只扫描并更新deleted_flag为true的行（如果有索引的话，扫描速度会更快）
• 生成的WAL日志量大幅减少
• 锁表/锁行的时间更短，对业务影响更小

如果你的大部分表中deleted_flag为true的行占比很低，这个改动能直接把执行时间压缩到原来的几分之一甚至几十分之一。

进阶优化方案（针对生产环境频繁执行的场景） #

除了加WHERE条件，还有几个关键优化点能进一步提升性能：

1. 给deleted_flag字段加索引 #

如果这些表还没给deleted_flag加索引，一定要加上！索引能让WHERE条件的扫描速度从全表扫描变成快速定位：

-- 单表创建索引（生产环境建议用CONCURRENTLY避免锁表）
CREATE INDEX CONCURRENTLY idx_{表名}_deleted_flag ON customer.{表名}(deleted_flag);

-- 动态SQL批量创建索引（同样用CONCURRENTLY）
DO $$ DECLARE
    rec RECORD;
BEGIN
    FOR rec IN (
        SELECT relname AS table_name, nspname AS schema_name
        FROM pg_catalog.pg_class c
        JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
        WHERE relkind = 'r' 
          AND n.nspname = 'customer' 
          AND relname NOT LIKE 'v_%'
          AND EXISTS (
              SELECT 1 FROM pg_catalog.pg_attribute a
              WHERE a.attrelid = c.oid 
                AND a.attname = 'deleted_flag' 
                AND NOT a.attisdropped
          )
          AND NOT EXISTS (
              SELECT 1 FROM pg_catalog.pg_index i
              WHERE i.indrelid = c.oid
                AND (i.indkey::text LIKE '%' || (SELECT attnum FROM pg_catalog.pg_attribute WHERE attrelid = c.oid AND attname = 'deleted_flag') || '%')
          )
    ) LOOP
        EXECUTE format('CREATE INDEX CONCURRENTLY idx_%I_deleted_flag ON %I.%I(deleted_flag)', rec.table_name, rec.schema_name, rec.table_name);
    END LOOP;
END $$;

注意：CONCURRENTLY创建索引不会锁表，但执行时间会稍长，适合在业务低峰期操作。

2. 用更快的系统表获取表列表 #

原脚本用information_schema.columns查询表列表，这个视图的查询速度比较慢（因为它是基于多个系统表的复杂视图）。换成pg_catalog下的原生系统表，能大幅提升获取表列表的速度：

-- 替换原脚本中的cursor查询
SELECT relname AS t_name, nspname AS t_schema
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE relkind = 'r' -- 只选普通表
  AND n.nspname = 'customer' 
  AND relname NOT LIKE 'v_%'
  AND EXISTS (
      SELECT 1 FROM pg_catalog.pg_attribute a
      WHERE a.attrelid = c.oid 
        AND a.attname = 'deleted_flag' 
        AND NOT a.attisdropped -- 排除已删除的字段
  );

3. 分批次更新大表 #

如果某些单表的数据量特别大（比如几百万甚至上千万行），即使加了WHERE条件，一次更新还是会占用大量资源。可以改成按主键或其他字段分批次更新：

-- 示例：按主键id分批次更新某张表
DO $$ DECLARE
    batch_size INT := 1000;
    max_id INT;
    current_id INT := 0;
BEGIN
    SELECT MAX(id) INTO max_id FROM customer.large_table;
    WHILE current_id < max_id LOOP
        UPDATE customer.large_table 
        SET deleted_flag = false 
        WHERE deleted_flag = true 
          AND id > current_id 
          AND id <= current_id + batch_size;
        COMMIT; -- 每批次提交，避免长事务
        current_id := current_id + batch_size;
    END LOOP;
END $$;

这个方法能减少单批次更新的锁范围和WAL生成量，降低对业务的影响。

4. 临时禁用触发器/约束（谨慎操作） #

如果这些表有触发器（比如更新日志触发器）或外键约束，更新时会触发额外的逻辑，拖慢速度。如果确认更新deleted_flag不会影响业务逻辑，可以临时禁用它们：

-- 临时禁用触发器
ALTER TABLE customer.{表名} DISABLE TRIGGER ALL;
-- 更新完成后恢复
ALTER TABLE customer.{表名} ENABLE TRIGGER ALL;

注意：这个操作有风险，一定要在业务低峰期执行，并且提前在测试环境验证！

优化后的完整脚本 #

结合以上优化点，最终的脚本可以写成这样：

DO $$ DECLARE
    t_record RECORD;
BEGIN
    -- 用pg_catalog快速获取目标表列表
    FOR t_record IN (
        SELECT relname AS table_name, nspname AS schema_name
        FROM pg_catalog.pg_class c
        JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
        WHERE relkind = 'r' 
          AND n.nspname = 'customer' 
          AND relname NOT LIKE 'v_%'
          AND EXISTS (
              SELECT 1 FROM pg_catalog.pg_attribute a
              WHERE a.attrelid = c.oid 
                AND a.attname = 'deleted_flag' 
                AND NOT a.attisdropped
          )
    ) LOOP
        -- 带WHERE条件的UPDATE，仅修改需要更新的行
        EXECUTE format(
            'UPDATE %I.%I SET deleted_flag = false WHERE deleted_flag = true',
            t_record.schema_name,
            t_record.table_name
        );
        -- 如果是大表，建议替换成分批次更新的逻辑
    END LOOP;
END $$;

生产环境执行注意事项 #

• 一定要在业务低峰期执行，避免影响正常业务
• 先在测试环境验证脚本的正确性和性能
• 监控数据库的CPU、磁盘IO、WAL生成量以及锁情况
• 如果是超大规模的表，建议单独处理，避免拖慢整个批量任务

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