From: Csaba Nagy <nagy@ecircle-ag.com>
To: Christian Paul B. Cosinas <cpc@cybees.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Deleting Records
Date: Thu, 20 Oct 2005 10:49:04 +0200
Message-ID: <1129798144.27587.152.camel@coppola.muc.ecircle.de> (raw)
In-Reply-To: <002701c5d552$5bc7ea30$1e21100a@ghwk02002147>
References: <002701c5d552$5bc7ea30$1e21100a@ghwk02002147>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagy@ecircle-ag.com, cpc@cybees.com
Subject: Re: Deleting Records
In-Reply-To: <1129798144.27587.152.camel@coppola.muc.ecircle.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox