PostgreSQL中带LEFT JOIN的DELETE语句如何添加LIMIT实现批量删除？

方法一：使用IN子查询指定待删除记录ID #

通过子查询筛选出符合条件且带LIMIT的记录主键，再删除对应数据：

DELETE FROM roster_validationtaskerror
WHERE id IN (
    SELECT rvte.id
    FROM roster_validationtaskerror AS rvte
    LEFT JOIN roster_validationtask AS rvt ON rvt.id = rvte.parent_task_id
    LEFT JOIN roster_validation AS rv ON rv.id = rvt.validation_id
    WHERE rv.id = 10
    LIMIT 1000 -- 自定义每次删除的记录数
);

方法二：使用JOIN关联待删除子集（效率更高） #

针对大数据量场景，JOIN方式的性能通常优于IN子查询，写法如下：

DELETE FROM roster_validationtaskerror AS target
USING (
    SELECT rvte.id
    FROM roster_validationtaskerror AS rvte
    LEFT JOIN roster_validationtask AS rvt ON rvt.id = rvte.parent_task_id
    LEFT JOIN roster_validation AS rv ON rv.id = rvt.validation_id
    WHERE rv.id = 10
    LIMIT 1000
) AS to_delete
WHERE target.id = to_delete.id;

循环批量删除全量符合条件的记录 #

由于你有1200万条待删记录，单次删除无法完成，可通过PL/pgSQL循环执行删除语句，直到所有符合条件的记录被清理：

DO $$
DECLARE
    deleted_count INT;
BEGIN
    LOOP
        DELETE FROM roster_validationtaskerror AS target
        USING (
            SELECT rvte.id
            FROM roster_validationtaskerror AS rvte
            LEFT JOIN roster_validationtask AS rvt ON rvt.id = rvte.parent_task_id
            LEFT JOIN roster_validation AS rv ON rv.id = rvt.validation_id
            WHERE rv.id = 10
            LIMIT 1000
        ) AS to_delete
        WHERE target.id = to_delete.id;
        
        -- 获取本次删除的行数
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        -- 当删除行数为0时退出循环
        EXIT WHEN deleted_count = 0;
        -- 可选：每次删除后暂停1秒，降低数据库负载
        -- PERFORM pg_sleep(1);
    END LOOP;
END $$;

注意事项 #

• 确保roster_validationtaskerror表的id字段是主键或有单独索引，否则子查询和删除操作会极慢；如果没有索引，建议先创建临时索引，删除完成后再删除索引。
• 调整LIMIT的数值：根据数据库性能调整每次删除的记录数，比如从1000、5000逐步测试，找到最优值。

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