Skip site navigation (1) Skip section navigation (2)

Re: Deleting Records

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: "Christian Paul B(dot) Cosinas" <cpc(at)cybees(dot)com>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Deleting Records
Date: 2005-10-20 08:49:04
Message-ID: 1129798144.27587.152.camel@coppola.muc.ecircle.de (view raw or flat)
Thread:
Lists: 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


In response to

pgsql-performance by date

Next:From: Nörder-TuitjeDate: 2005-10-20 08:50:50
Subject: Re: Deleting Records
Previous:From: Christian Paul B. CosinasDate: 2005-10-20 08:43:34
Subject: Deleting Records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group