postgresql - 在 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% 的数据大约需要一分钟。所以这对于大数据来说需要很长时间。请建议我做的事情是否正确，或者是否有更好的方法。

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;

如果需要，您可以在单笔交易中完成。