PostgreSQL中删除大量随机行的最佳方法

Question

我有一个包含约900K行的表格。

我想要删除其中大约90%的行。

我尝试使用TABLESAMPLE随机选择这些行，但性能提升不大。

以下是我尝试过的查询和它们的执行时间：

sql> DELETE FROM users WHERE id IN (
SELECT id FROM users ORDER BY random() LIMIT 5000
)
[2017-11-22 11:35:39] 5000 rows affected in 1m 11s 55ms


sql> DELETE FROM users WHERE id IN (
SELECT id FROM users TABLESAMPLE BERNOULLI (5)
)
[2017-11-22 11:55:07] 5845 rows affected in 1m 13s 666ms


sql> DELETE FROM users WHERE id IN (
SELECT id FROM users TABLESAMPLE SYSTEM (5)
)
[2017-11-22 11:57:59] 5486 rows affected in 1m 4s 574ms

仅删除5%的数据需要大约1分钟时间。因此，对于大型数据来说，这将需要很长时间。请建议我是否做得正确，或者是否有更好的方法。

- Pooja

Answer 1

删除大量行数据总是很慢的。你如何识别它们不会有太大的区别。

相比于删除大量数据，通常更快的方法是创建一个新表，其中包含您想要保留的行，例如：

create table users_to_keep
as
select *
from users
tablesample system (10);

然后截断原始表并插入你存储的行：

truncate table users;
insert into users
select *
from users_to_keep;

如果你愿意，你可以在一次交易中完成这个操作。

- user330315

---

选择的问题在于这个表有很多依赖表，我也想从它们那里获取数据。维护引用完整性将会变得非常麻烦。相反，我想删除行，这样级联就会负责从子表中删除它们。 - Pooja

tablesample system (10); 的目的是什么？ - abbas

Answer 2

正如a_horse_with_no_name所指出的那样，随机选择本身是一个相对较小的因素。与删除相关的大部分成本（例如外键检查）是无法避免的。

唯一需要注意的不必要开销是在DELETE语句中基于id进行查找；在随机选择步骤中已经访问了该行，现在你又通过id索引再次查找它。

相反，你可以使用表示行物理位置的隐藏ctid列来执行查找。

DELETE FROM users WHERE ctid = ANY(ARRAY(
  SELECT ctid FROM users TABLESAMPLE SYSTEM (5)
))

这在人工测试中使我获得了大约6倍的加速，尽管在大多数实际情况下，它可能会被其他成本所掩盖。

- Nick Barnes