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.