PostgreSQL日志表去重优化：保留最新记录的高效方案咨询

针对你500万条记录的日志表删除重复（保留最大id）的需求，现有查询执行缓慢的核心原因要么是嵌套子查询导致的逐行聚合，要么是全表排序带来的高成本。下面给出几个针对性的优化方案，从索引优化、查询改写、分批执行三个维度入手：

一、先创建复合覆盖索引（关键前提） #

不管用哪种删除方式，首先要确保数据库能高效定位重复分组的最大id。现有执行计划中虽然有object_log_idx1，但可以优化为覆盖索引，让分组和聚合操作完全在索引中完成，避免回表查询：

-- 创建覆盖分组字段+id的索引，PostgreSQL 11+支持INCLUDE语法
CREATE INDEX idx_object_log_duplicate ON sys.object_log (object_type_name, object_id, property_name) INCLUDE (id);

-- 或者创建带排序的复合索引，让MAX(id)直接从索引提取
CREATE INDEX idx_object_log_duplicate_desc ON sys.object_log (object_type_name, object_id, property_name, id DESC);

创建索引后记得更新统计信息，帮助优化器生成更优计划：

ANALYZE sys.object_log;

二、改写查询：用分组聚合直接定位要删除的记录 #

替换原有的嵌套子查询或窗口函数写法，改用分组找最大id+批量删除的逻辑，避免逐行计算或全表排序：

方案1：DELETE + USING 分组查询（推荐） #

这个写法直接通过分组获取每个重复组的保留id，然后批量删除同组内的其他记录：

DELETE FROM sys.object_log t
USING (
    SELECT object_type_name, object_id, property_name, MAX(id) AS max_id
    FROM sys.object_log
    GROUP BY object_type_name, object_id, property_name
) AS keep
WHERE t.object_type_name = keep.object_type_name
  AND t.object_id = keep.object_id
  AND t.property_name = keep.property_name
  AND t.id != keep.max_id;

优势：分组聚合只执行一次，配合前面的覆盖索引，分组操作几乎不会产生额外IO，执行效率远高于嵌套子查询。

方案2：临时表+NOT EXISTS（适合超大规模表） #

如果表的规模超过千万级，可以先把要保留的id存入临时表并建索引，再执行删除，进一步降低锁表时间：

-- 1. 生成要保留的id临时表
CREATE TEMP TABLE keep_ids AS
SELECT MAX(id) AS id
FROM sys.object_log
GROUP BY object_type_name, object_id, property_name;

-- 2. 给临时表建索引加速查询
CREATE INDEX idx_keep_ids ON keep_ids(id);

-- 3. 删除不在保留列表中的记录
DELETE FROM sys.object_log t
WHERE NOT EXISTS (SELECT 1 FROM keep_ids k WHERE k.id = t.id);

三、分批删除：避免长时间锁表和日志暴涨 #

如果一次性删除的记录数过多（比如超过百万条），会导致事务日志过大、表锁时间过长，影响业务。可以用LIMIT分批删除：

-- 循环执行这个语句，直到返回0行受影响
WITH to_delete AS (
    SELECT t.id
    FROM sys.object_log t
    JOIN (
        SELECT object_type_name, object_id, property_name, MAX(id) AS max_id
        FROM sys.object_log
        GROUP BY object_type_name, object_id, property_name
    ) keep ON t.object_type_name = keep.object_type_name
           AND t.object_id = keep.object_id
           AND t.property_name = keep.property_name
           AND t.id != keep.max_id
    LIMIT 10000 -- 每次删1万条，可根据服务器性能调整
)
DELETE FROM sys.object_log
WHERE id IN (SELECT id FROM to_delete);

四、优化原有窗口函数写法 #

如果你偏好窗口函数的方式，可以通过索引让排序操作跳过全表扫描：

在创建了idx_object_log_duplicate索引后，原窗口函数查询的执行计划会利用索引有序性，避免全表排序。你也可以改写为更高效的形式：

DELETE FROM sys.object_log
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               MAX(id) OVER (PARTITION BY object_type_name, object_id, property_name) AS max_id
        FROM sys.object_log
    ) t
    WHERE id != max_id
);

这个写法用MAX() OVER()代替ROW_NUMBER()，不需要排序，配合索引可以大幅降低执行成本。

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