Re:PostgreSQL EXPLAIN Output Explained
by michristofides on Friday May 28, 2021 @05:57PM
(#27320999)
EXPLAIN ANALYZE would have shown you referential integrity (RI) triggers taking most of the time, but it’s still a bit of a leap to work out that it’s due to missing foreign key indexes if you don’t already know
The table I was deleting from had 20 foreign key constraints referencing it, and a couple of them didn't have an index on the referencing column and were big (a few million rows). Added indexes to all of them, took a couple of minutes to build, and the DELETE ran in a few seconds.
Sometimes the answer to a performance issue can't be found in EXPLAIN. And always remember to properly index your foreign key constraints.