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.

Rohit Gupta
  • 1,626
  • 6
  • 17
  • 19
Will Newton
  • 1
  • 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 '23 at 13:43

1 Answers1

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.

Will Newton
  • 1
  • 1