如何优化PostgreSQL中执行超48小时未完成的删除查询？

原查询执行缓慢的核心原因 #

原语句采用三层嵌套子查询写法，在PostgreSQL 11版本下，针对千万级、百万级的大表场景，执行计划通常会生成效率极低的嵌套循环逻辑，全程走全表扫描，每次字段匹配都要遍历全表数据，累计开销被无限放大，才会出现执行超过2天未完成的情况。

已调整语句的优化逻辑 #

修改后的语句已经实现了核心优化，性能提升的主要原因有两点：

• 将嵌套NOT EXISTS子查询改写为LEFT JOIN + 空值匹配的关联查询写法，PostgreSQL对于这类关联查询的执行计划优化更成熟，会优先选择哈希关联、归并关联等高效关联逻辑，执行效率比嵌套循环高几个数量级
• 新增LIMIT 1000实现分批删除，避免单次删除操作锁定表的时间过长，同时大幅减少单次事务的WAL日志生成量，不会出现长事务阻塞其他业务操作的问题，只要循环执行该语句即可清理完所有符合条件的数据

可进一步提升性能的补充方案 #

1. 新增必要索引 #

针对关联和匹配字段创建索引，避免全表扫描：

• 给account_message表的message_id字段创建普通索引，加速删除行的定位：

CREATE INDEX idx_account_message_message_id ON account_message(message_id);

• 给message表的username字段创建普通索引，加速和customer表的关联匹配：

CREATE INDEX idx_message_username ON message(username);

2. 大比例数据删除优化 #

如果待删除的数据占account_message表总数据量的30%以上，可以采用临时表迁移的方案：先将需要保留的数据写入临时表，清空原表后再将临时表数据迁回，该方案比逐行删除的效率高5~10倍。

3. 辅助优化操作 #

执行删除前可以临时关闭表上的非必要索引、触发器，删除完成后再重建，减少删除过程中的额外开销；同时可以根据服务器负载适当调整LIMIT的数值，比如调整为5000~10000，平衡执行次数和单次执行时长。

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