PostgreSQL删除重复数据查询仅在部分环境挂起求助

问题背景 #

我有一个存储JSONB数据的PostgreSQL 12大表，需要基于aCol和bCol创建组合唯一索引，但表中存在意外重复数据，必须先删除重复项。该删除操作在2台服务器（Dev、Prod，schema一致）上数分钟即可完成，但在第3台服务器上运行2小时都无法结束。

已统一所有环境的资源配置（内存、磁盘、CPU、effective_io_concurrency等），且故障服务器的表规模更小：仅40万条记录（约2GB），而正常完成的服务器之一有近200万条记录（约9GB）。将故障环境的CSV备份导入全新同schema服务器，问题依然存在，说明不是服务器环境本身的问题。

现有删除查询语句 #

DELETE FROM aTable a USING aTable b
WHERE a.aCol = b.aCol
    AND a.bCol = b.bCol
    AND a.id != b.id
    AND a.created_at <= b.created_at

执行计划对比 #

正常环境执行计划 #

Delete on aTable a  (cost=800504.63..5706702.06 rows=72439076 width=12)
  ->  Merge Join  (cost=800504.63..5706702.06 rows=72439076 width=12)
        Merge Cond: ((a.aCol = b.aCol) AND (a.bCol = b.bCol))
        Join Filter: ((a.id <> b.id) AND (a.created_at <= b.created_at))
        ->  Sort  (cost=400252.31..404391.52 rows=1655683 width=50)
              Sort Key: a.aCol, a.bCol
              ->  Seq Scan on aTable a  (cost=0.00..184763.83 rows=1655683 width=50)
        ->  Materialize  (cost=400252.31..408530.73 rows=1655683 width=50)
              ->  Sort  (cost=400252.31..404391.52 rows=1655683 width=50)
                    Sort Key: b.aCol, b.bCol
                    ->  Seq Scan on aTable b  (cost=0.00..184763.83 rows=1655683 width=50)

故障环境执行计划 #

Delete on aTable a  (cost=0.00..1794266.00 rows=59613596 width=12)
  ->  Nested Loop  (cost=0.00..1794266.00 rows=59613596 width=12)
        ->  Seq Scan on aTable a  (cost=0.00..40365.00 rows=395600 width=50)
        ->  Index Scan using idx_aTable_aCol on aTable b  (cost=0.00..4.42 rows=1 width=50)
              Index Cond: (aCol = a.aCol)
              Filter: ((a.id <> id) AND (a.created_at <= created_at) AND (a.bCol = bCol))

故障环境使用aCol上的哈希索引，且所有环境索引一致，但该索引仅支持等值查询，无法与bCol组合过滤，反而拖慢了查询效率。

原因分析 #

1. 执行计划选择错误：正常环境采用Merge Join，先按aCol+bCol排序后合并，能高效匹配重复项；故障环境采用Nested Loop，对每条a表记录通过哈希索引查询b表后再过滤bCol，若aCol重复度极高，会导致循环次数暴增，IO和计算量剧增。
2. 统计信息偏差：PostgreSQL优化器依赖表统计数据，故障环境的统计信息可能过时或不准确，导致优化器错误判断索引扫描的行数（执行计划中显示rows=1，实际远高于此），进而选择低效的Nested Loop。
3. 哈希索引局限性：idx_aTable_aCol为哈希索引，仅能处理等值查询，无法提供有序数据支持Merge Join，且无法与bCol组合过滤，额外增加了过滤开销。

优化方案 #

方案1：强制使用Merge Join #

临时关闭Nested Loop，让优化器选择Merge Join：

SET enable_nestloop = off;

DELETE FROM aTable a USING aTable b
WHERE a.aCol = b.aCol
    AND a.bCol = b.bCol
    AND a.id != b.id
    AND a.created_at <= b.created_at;

SET enable_nestloop = on; -- 执行完成后恢复默认设置

方案2：分组聚合删除重复（更高效） #

先找到每个(aCol,bCol)组要保留的最新记录，再批量删除其他重复项：

-- 删除重复数据，仅保留每个组合组中created_at最新的记录
DELETE FROM aTable
WHERE id NOT IN (
    SELECT MAX(id)
    FROM aTable
    GROUP BY aCol, bCol
);

若数据量较大，可先创建临时组合索引提升分组效率：

CREATE INDEX idx_temp_aTable_aCol_bCol ON aTable (aCol, bCol);

-- 执行上述删除语句

DROP INDEX idx_temp_aTable_aCol_bCol;

方案3：分批删除，避免资源耗尽 #

针对大量重复数据，分批删除以减少锁表时间和资源占用：

-- 每次删除1000条重复记录，重复执行直到返回0行受影响
WITH duplicates AS (
    SELECT a.id
    FROM aTable a
    JOIN (
        SELECT aCol, bCol, MAX(created_at) AS latest_created
        FROM aTable
        GROUP BY aCol, bCol
        HAVING COUNT(*) > 1
    ) b ON a.aCol = b.aCol AND a.bCol = b.bCol
    WHERE a.created_at < b.latest_created
    LIMIT 1000
)
DELETE FROM aTable WHERE id IN (SELECT id FROM duplicates);

方案4：更新表统计信息 #

更新表统计数据，让优化器生成更合理的执行计划：

ANALYZE aTable;

更新后重新执行原删除查询，观察是否会选择高效的Merge Join。

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