Postgres slow running delete query
... 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)