0

I have a number of tables relating to an entity and delete them in a transaction to make sure all are removed simultaneously:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
delete from table1 where entity_id = 1;
delete from table2 where entity_id = 1;
delete from table3 where entity_id = 1;
COMMIT;

I defer constraints because of a variety of foreign key relationships, including in one table a circular relationship (a self-referential foreign key).

The COMMIT runs extremely slowly, I assume because of checking the deferred constraints.

Is there a way to figure out what is going on here? Explain analyze doesn't seem to work with deferred constraints.

As far as I can tell all my foreign keys have indexes on them.

Improve this question
1
  • I changed the constraints deferal to only defer the self-referential constraints (table1 -> table1 pk) and the performance is still bad. Commit gets stuck for hours and there's only millions of records. Looks like the self-referential constraints are the issue?
    – Will Newton
    Aug 25 at 13:43

1 Answer 1

Reset to default
0

It turns out the issue was that the foreign keys needed indexes on both ends, not just on the primary key but the reference to the primary key.

Improve this answer
1

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