我想知道最有效的方法是从PostgreSQL删除大量行,这一过程每天都会是将数据批量导入(插入和删除的增量)到表中的一个重复任务。可能要删除数千行,甚至可能要删除几百万行。

我有一个主键文件,每行一个。我所考虑的两个选项与以下内容类似,但我对PostgreSQL的内部知识了解不足/不了解,无法做出明智的决定,这是最佳选择。


对文件中的每一行执行一次DELETE查询,并在主键上添加一个简单的WHERE(或使用n子句将删除的IN()分组)
使用COPY命令将主键导入到临时表中,然后使用连接从主表中删除

任何建议将不胜感激!

评论

此处已更详细地回答了相同的问题:stackoverflow.com/a/8290958

万一其他人不知道(就像我直到最近一样),禁用或删除触发器(PostgreSQL在内部也使用约束)可以将批量删除的性能提高几个数量级。鉴于最初的问题提到了定期(每天)批量进口,我想这已经在相关表中完成了。

#1 楼

您的第二个选择要干净得多,并且将表现出色,值得这样做。您的替代方法是建立庞大的查询,这对计划和执行将是非常痛苦的。总的来说,让PostgreSQL在这里工作会更好。总的来说,我发现以您描述的方式可以对成千上万的行进行适当的更新,但是要避免这样做是一件重要的事情。选择并加入您的删除。

DELETE FROM foo WHERE id IN (select id from rows_to_delete);


在任何情况下,您都不要在一张大桌子上做以下事情:

DELETE FROM foo WHERE id NOT IN (select id from rows_to_keep);


这通常会导致嵌套循环的反连接会导致性能问题。如果您最终不得不走这条路,请改用以下方法:好的计划和坏的计划会有很大的不同。

这在更远的地方徘徊,但是我认为值得一提,因为从IN到NOT IN观看非常容易查询性能槽。

评论


这很有帮助,谢谢!但是,我发现在这种特殊情况下,使用“组合查询”更为有效。例如。 IN(从foo中选择ID,但从rows_to_keep中选择ID除外)参见postgresql.org/docs/9.4/static/queries-union.html

– Ufos
17年9月12日在13:31



#2 楼

我遇到了这个问题,因为我遇到了类似的问题。我正在清理具有300M +行的数据库,最终数据库将仅包含原始数据的30%。如果您遇到类似的情况,则实际上更容易插入新表并重新索引而不是删除。

做类似


通过在f​​oo和bar上正确建立索引,可以避免进行Seq扫描。