Subject: Re: Deleting Records
From: Csaba Nagy <nagy@ecircle-ag.com>
To: "Christian Paul B. Cosinas" <cpc@cybees.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: <002701c5d552$5bc7ea30$1e21100a@ghwk02002147>
References: <002701c5d552$5bc7ea30$1e21100a@ghwk02002147>
Content-Type: text/plain
Message-Id: <1129798144.27587.152.camel@coppola.muc.ecircle.de>
Mime-Version: 1.0
Date: Thu, 20 Oct 2005 10:49:04 +0200
Content-Transfer-Encoding: 7bit

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