Postgres运行缓慢的删除查询

Question

我们有一个包含超过62k行的表格。我们在其上运行一个非常简单的删除查询，但完成该操作需要45分钟：

DELETE FROM myTable WHERE createdtime < '2017-03-07 05:00:00.000'

我们尝试过的事情：

1- 在时间戳列上添加索引，但没有帮助。

2- 使用函数批量删除20或50行，仍然非常慢。

3- 删除所有外键约束，这有所帮助并将时间缩短到几秒钟，但在生产数据库中我们无法安全地执行此操作，因为它会锁定表格并防止读写操作，而交易正在运行。

我不相信这个查询需要这么长时间才能完成。欢迎任何建议。

- infiniteLoop

---

放弃了所有引用该表的外键约束。这些FK是否有支持它们的索引（在“其他”表上）？ - joop

他说的很明显，时间花在查找外键约束上。 - paqash

性能问题应该包括EXPLAIN ANALYZE以及有关表大小、索引、当前时间性能、期望时间等方面的一些信息。慢是一个相对的概念，我们需要一个实际值来进行比较。同时，请阅读如何提问中的MySQL相关内容。 - e4c5

@joop，我简直不敢相信我自己没有想到！请将其添加为答案，以便我可以将其标记为已解决，因为这解决了问题。谢谢！ - infiniteLoop

我不知道外键需要索引，花了好几个小时想知道为什么在生产表上所有操作都非常缓慢，而在测试我的查询的复制数据表上操作非常快。 - JohnEye

Answer 1

... 删除了所有引用该表的外键约束

确保这些外键在另一个表上有支持它们的索引。当你删除时，级联的外键将不得不检查所有可能引用这一行的其他表的FK列。

---

-- 示例：

CREATE TABLE team(
        id INTEGER NOT NULL PRIMARY KEY
        , name varchar UNIQUE
        );

CREATE TABLE player(
        id INTEGER NOT NULL PRIMARY KEY
        , team_id integer REFERENCES team(id)
        , name varchar UNIQUE
        );

现在，如果一个team被删除了，FK（外键）约束将不得不检查是否有任何玩家引用这个team_id。（并适当地级联） 在这种情况下，对FK上的支持索引将有助于DBMS：

CREATE index ON player(team_id);

will help在这里有点太弱了。对于每个非平凡的情况，都绝对需要一个支持性的索引。（即使FK约束的action为ON UPDATE NO ACTION ON DELETE NO ACTION，似乎也是如此）

(Note: This is the translated version of the original text. It is not a revision or editing of the original text.)

- joop

---

@infiniteLoop：这一点并不令人惊讶，因为数据库需要确保没有任何玩家与您要删除的“team”相关联。而这个查找实际上是使用“select * from player where team_id = ...”完成的，如果没有索引，那么就需要在player表上进行Seq扫描。 - user330315

@a_horse_with_no_name：好的。我以为ON DELETE CASCADE/ NO ACTION是决定是否应该防止删除的东西。 - infiniteLoop

.. I don't need a parachute, because I don't expect the plane to crash ... - joop

@infiniteLoop：不，这个条件确定如果子行存在应该采取哪种操作 - 但是如果存在外键，则必须测试子行。 - user330315

@a_horse_with_no_name：我的情况正好相反。我正在尝试删除“player”表，这让我花费了很长时间。在这方面，我应该在“team”还是“player”中创建索引？ - as - if