PostgreSQL 12中耗时5小时的DELETE查询优化咨询

1. 为关联字段创建复合索引 #

首先确保两张表的(ddate, itemno)字段都创建复合索引，这能大幅提升关联查询的匹配速度：

-- 为table1创建复合索引
CREATE INDEX idx_table1_date_item ON table1(ddate, itemno);
-- 为table2创建复合索引
CREATE INDEX idx_table2_date_item ON table2(ddate, itemno);

如果这两个表已存在对应复合索引，可跳过此步骤。

2. 改用NOT EXISTS替代NOT IN #

NOT IN在大数据量场景下的执行计划效率通常不如NOT EXISTS，尤其是子查询存在NULL值时（复合键场景下仍推荐替换）：

DELETE FROM table2 t2
WHERE NOT EXISTS (
    SELECT 1 FROM table1 t1
    WHERE t1.ddate = t2.ddate AND t1.itemno = t2.itemno
);

PostgreSQL对NOT EXISTS的执行计划优化更友好，能更好地利用索引完成关联匹配。

3. 分批删除（避免长事务与锁表） #

一次性删除大量数据会导致长时间锁表，还会生成大量WAL日志拖慢性能，可通过分批删除缓解：

-- 每次删除1000条，循环执行直到无匹配数据
WHILE EXISTS (
    SELECT 1 FROM table2 t2
    WHERE NOT EXISTS (
        SELECT 1 FROM table1 t1
        WHERE t1.ddate = t2.ddate AND t1.itemno = t2.itemno
    )
) LOOP
    DELETE FROM table2 t2
    WHERE NOT EXISTS (
        SELECT 1 FROM table1 t1
        WHERE t1.ddate = t2.ddate AND t1.itemno = t2.itemno
    )
    LIMIT 1000;
    COMMIT; -- 每批提交释放锁
END LOOP;

可根据服务器性能调整LIMIT值（如5000或10000），平衡删除速度与系统负载。

4. 临时表预存保留数据（极端场景优化） #

若上述方法仍未达标，可通过临时表重建目标表：

-- 创建临时表存储table2中需要保留的数据
CREATE TEMP TABLE temp_table2 AS
SELECT t2.* FROM table2 t2
JOIN table1 t1 ON t1.ddate = t2.ddate AND t1.itemno = t2.itemno;

-- 清空原表（需提前确认数据备份）
TRUNCATE TABLE table2;

-- 将临时表数据导回原表
INSERT INTO table2 SELECT * FROM temp_table2;

-- 清理临时表
DROP TABLE temp_table2;

此方法避免了大量删除操作的开销，但操作期间table2会被清空，建议在维护窗口执行。

5. 临时调整PostgreSQL配置参数 #

若服务器资源充足，可临时调整以下参数提升性能：

-- 增大排序/哈希连接可用内存，避免磁盘交换
SET work_mem = '64MB';
-- 增大维护操作可用内存（如创建索引时）
SET maintenance_work_mem = '256MB';

以上为会话级参数，重启数据库后会恢复默认值，无需修改配置文件。

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