4

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:

  • 2
    You may have deleted child rows, but Postgres doesn't know that and it still has to search for them. If you don't have indexes supporting foreign keys you're in for a lot of pain and suffering. – mustaccio Aug 14 '17 at 19:06
  • @mustaccio point taken. quick fix was to remove the fks and add them back (and the index) – salient Aug 14 '17 at 19:14
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.