Postgres slow running delete query

Question

We have a table which has just over 62k rows. We are running a very simple delete query on it which takes 45 minutes to complete:

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

Things we have tried:

1- Added an index on timestamp column, which did not help.

2- Removed the rows in batches of 20 or 50 using a function, which was still awfully slow.

3- Dropped all the foreign key constraints referencing this table and its own primary key constraint, which did help and reduced the time to a few seconds but we can't safely do this on our production database as it will lock the tables and prevent reads and writes while the transaction is running.

I refuse to believe that it's normal for this query to take this long to complete. Any suggestions are appreciated.

`Dropped all the foreign key constraints referencing this table` Do these FK's have indexes supporting them (on the *other* table)? — joop, Mar 09 '17 at 17:46
What he said. Obviously the time is spent looking up the FK constraints — paqash, Mar 09 '17 at 19:50
Performance questions should include EXPLAIN ANALYZE and some information about table size, index, current time performance, desire time, etc. Slow is a relative term and we need a real value to compare. MySQL Also Please read [How-to-Ask](http://stackoverflow.com/help/how-to-ask) — e4c5, Mar 10 '17 at 05:57
@joop I cannot believe I did not think of that myself! Please add that as an answer so I can mark it as solved, as that resolved the issue. Thank you! — infiniteLoop, Mar 10 '17 at 10:07
I had no idea foreign keys needed indexes and spent hours wondering why all the operations are extremely slow on production tables while they were quick on the tables with copied data where I tested my queries. — JohnEye, Feb 08 '22 at 14:23

score 26 · Accepted Answer · edited Mar 10 '17 at 10:41

26

... Dropped all the foreign key constraints referencing this table

Make sure these FK's have indexes supporting them (on the other table). When you delete, the (cascading) FK will have to check all the FK columns from other tables that could refer to this row.

-- example:

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
        );

Now, if a team is deleted, the FK constraint will have to check if there are any players that refer to this team_id. (and cascade appropiately) In that case, a supportive index on the FK will help the DBMS:

CREATE index ON player(team_id);

will help is a bit too weak here. A supportive index is absolutely needed for every non-trivial case. (even if the FK constraint has ON UPDATE NO ACTION ON DELETE NO ACTION as its action, so it seems)

edited Mar 10 '17 at 10:41

answered Mar 10 '17 at 10:12

joop

4,330
1
15
26

Just for everyone's benefit, even though my FKs were all set to ON UPDATE NO ACTION ON DELETE NO ACTION, this check seemed to have happened anyway resulting in the slow running query, which is surprising to me. – infiniteLoop Mar 10 '17 at 10:22
@infiniteLoop: that is not surprising at all, because the database needs to make sure that there is no player around that references the `team` you are deleting. And that lookup is essentially done using a `select * from player where team_id = ...` if there is no index, that is going to need a Seq Scan on the player table. – Mar 10 '17 at 10:42
@a_horse_with_no_name: but why would it need to do that check if it's not planning to remove the child rows that are associated with the parent row being deleted? – infiniteLoop Mar 10 '17 at 13:12
@infiniteLoop Because it needs to _prevent_ the parent to be deleted if there are child rows – Mar 10 '17 at 13:16
@a_horse_with_no_name: Okay. I thought the ON DELETE CASCADE/ NO ACTION stuff is what determines whether that deletion should be prevented or not. – infiniteLoop Mar 10 '17 at 13:22
`.. I don't need a parachute, because I don't expect the plane to crash ...` – joop Mar 10 '17 at 13:41
2

@infiniteLoop: no that condition determines which action should be taken _if_ child rows exist - but the test for the child rows **must** be done if a foreign key is present – Mar 10 '17 at 14:13
@a_horse_with_no_name: I see. Thanks for the clarification :-) – infiniteLoop Mar 10 '17 at 14:18
1

@a_horse_with_no_name: my situation is opposite. I am trying to delete the `player` table and this taking me forever. In this regard where do i create index in `team` or in `player` – as - if Jun 27 '18 at 13:57

Postgres slow running delete query

1 Answers1

Linked