On 29/11/2011, at 09:13, Tom Lane wrote: > "Herouth Maoz" <hero...@unicell.co.il> writes: >> 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? -- חרות מעוז יוניסל פתרונות סלולריים מתקדמים ☎ 03-5181717 שלוחה 742
