One thing I learned about EXPLAIN this week is that it doesn't show constraint checks. I was trying to delete about 40k rows from a table and it was taking hours and I couldn't figure out why. ANALYZE EXPLAIN showed nothing indicating anything about reading any of the other tables than the FROM and the USING table.
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.
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.