I was instructed to delete old records from one of the tables in our production system. The deletion took hours and I had to stop it in mid-operation and reschedule it as a night job. But then I had to do the same when I got up in the morning and it was still running.
I got an interesting clue, though, when I canceled the deletion the second time around. I got the following error message:
Cancel request sent
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."sent_messages" x WHERE $1 OPERATOR(pg_catalog.=) "subscription_id" FOR SHARE OF x"
Yup, that's a clue all right. I'll bet a nickel that you don't have an index on the foreign key's referencing column (ie, sent_messages.subscription_id). That means each delete in the referenced table has to seqscan the referencing table to see if the delete would result in an FK violation.
Makes sense. But shouldn't that be figured into the EXPLAIN plan?
