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

delete from table1 where entity_id = 1;
delete from table2 where entity_id = 1;
delete from table3 where entity_id = 1;

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.

  • 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? Aug 25, 2023 at 13:43

1 Answer 1


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.


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