sql - 我怎样才能快速删除 postgres 中的行？

Question

我有一个包含 17,000,000 行的表。我需要在某些条件下删除 500,000。现在我有一个 500,000 行的脚本，看起来像

delete from table where name = 'John' and date = '2010-08-04';
delete from table  where name = 'John' and date = '2010-08-05';
delete from table  where name = 'Adam' and date = '2010-08-06';

一行执行大约 2.5 秒。它太长了。我怎样才能提高速度？

Answer 1

如果名称和日期字段上没有索引，则尝试创建以下索引并尝试您的代码。

CREATE INDEX idx_table_name_date  ON table  (name, date)

如果可能，您还可以通过合并删除语句来减少删除语句的数量。

代替

delete from table where name = 'John' and date = '2010-08-04';
delete from table  where name = 'John' and date = '2010-08-05';

可以是:

delete from table where name = 'John' and date in('2010-08-04','2010-08-05');