PostgreSQL 9.5.2 DELETE taking forever

Question

I have a seemingly simple delete:

DELETE FROM table WHERE added_at < '2017-08-14'

on a table with ~20k rows (w ~10k affected rows), but it has been running for 2000+ seconds.

SELECT * FROM table WHERE added_at < '2017-08-14'

takes a few ms.

I have a few other tables with FOREIGN KEYs pointing to this id and I had some previous records pointing to it, but they have already been deleted.

I have tried to:

• check if the table is locked using the answer in https://stackoverflow.com/a/26596931/2171758
• terminate all backends
• VACUUM

Answer 1

Add a index to dependents tables (for eficient deletes)

If you have

create table "table"(
  id integer primary key,
  added_at date
);

create table other(
  other_id integer primary key,
  table_id integer,
  foreign key (table_id) references "table" (id)
);

and you delete rows from "table" postgres must search the other table for each row that you deletes in the "table". If you add an index in the other table postgres can search in the index.

CREATE INDEX ON other (table_id);

Now you can eficiently delete rows in "table".