1

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.

Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25
Will Newton
  • 21
  • 4

1 Answers1

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.

Will Newton
  • 21
  • 4