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.

Answer 1

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