Re: Deleting Records - Mailing list pgsql-performance

From Csaba Nagy
Subject Re: Deleting Records
Date
Msg-id 1129798144.27587.152.camel@coppola.muc.ecircle.de
Whole thread Raw
In response to Deleting Records  ("Christian Paul B. Cosinas" <cpc@cybees.com>)
List pgsql-performance
Christian,

Do you have foreign keys pointing to your table with ON CASCADE... ?
Cause in that case you're not only deleting your 22000 records, but the
whole tree of cascades. And if you don't have an index on one of those
foreign keys, then you might have a sequential scan of the child table
on each deleted row... I would check the foreign keys.

HTH,
Csaba.


On Thu, 2005-10-20 at 10:43, Christian Paul B. Cosinas wrote:
> Hi!
>
> I'm experiencing a very slow deletion of records. Which I thin is not right.
> I have a Dual Xeon Server with 6gig Memory.
> I am only deleting about 22,000 records but it took me more than 1 hour to
> finish this.
>
> What could possibly I do so that I can make this fast?
>
> Here is the code inside my function:
>
>     FOR temp_rec IN SELECT * FROM item_qc_doer LOOP
>         DELETE FROM qc_session WHERE item_id = temp_rec.item_id;
>         DELETE FROM item_qc_doer WHERE item_id = temp_rec.item_id;
>     END LOOP;
>
> Item_qc_oder table contains 22,000 records.
>
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


pgsql-performance by date:

Previous
From: "Christian Paul B. Cosinas"
Date:
Subject: Deleting Records
Next
From: Nörder-Tuitje, Marcus
Date:
Subject: Re: Deleting Records