pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDeleting Records
6+ messages / 5 participants
[nested] [flat]
* Deleting Records
@ 2005-10-20 08:43 Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-20 08:49 ` Re: Deleting Records Csaba Nagy <nagy@ecircle-ag.com>
2005-10-20 08:52 ` Re: Deleting Records Matteo Beccati <php@beccati.com>
2005-10-20 08:57 ` Re: Deleting Records Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-10-20 08:59 ` Re: Deleting Records Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 4 replies; 6+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-20 08:43 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Deleting Records
2005-10-20 08:43 Deleting Records Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-20 08:49 ` Csaba Nagy <nagy@ecircle-ag.com>
3 siblings, 0 replies; 6+ messages in thread
From: Csaba Nagy @ 2005-10-20 08:49 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Deleting Records
2005-10-20 08:43 Deleting Records Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-20 08:52 ` Matteo Beccati <php@beccati.com>
3 siblings, 0 replies; 6+ messages in thread
From: Matteo Beccati @ 2005-10-20 08:52 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
Hi,
> 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;
Qhat about just using:
DELETE FROM gc_session WHERE item_id IN
(SELECT item_id FROM item_qc_doer)
DELETE FROM item_qc_doer;
It doesn't make sense to run 2 x 22.000 separate delete statements
instead that only two...
And... What about using a foreing key?
Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Deleting Records
2005-10-20 08:43 Deleting Records Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-20 08:57 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
3 siblings, 0 replies; 6+ messages in thread
From: Christopher Kings-Lynne @ 2005-10-20 08:57 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
> 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'd check to see if i have foreign keys on those tables and if the
columns that refer to them are properly indexed. (For cascade delete or
even just checking restrict)
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Deleting Records
2005-10-20 08:43 Deleting Records Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-20 08:59 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
3 siblings, 0 replies; 6+ messages in thread
From: Christopher Kings-Lynne @ 2005-10-20 08:59 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
> 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.
Also, chekc you have an index on both those item_id columns.
Also, why don't you just not use the loop and do this instead:
DELETE FROM qc_session WHERE item_id IN (SELECT item_id FROM item_qc_doer);
DELETE FROM item_qc_doer;
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Deleting Records
@ 2005-10-20 08:50 Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>
0 siblings, 0 replies; 6+ messages in thread
From: Nörder-Tuitje, Marcus @ 2005-10-20 08:50 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; pgsql-performance
what about firing a
DELETE FROM qc_session S
WHERE EXISTS (SELECT *
FROM item_qc_doer i
WHERE i.item_id = s.item_id);
and
DELETE FROM item_qc_doer S
WHERE EXISTS (SELECT *
FROM item_qc_doer i
WHERE i.item_id = s.item_id);
this might be faster.
another way to speed up deletes might be disabling foreign keys.
also a SET ENABLE_SEQSCAN=FALSE; can speed up queries (force use of indices for access)
do you have a EXPLAIN for us ? do you have a index on item_id on your tables ?
questions by questions ;-)
mfg
-----Ursprüngliche Nachricht-----
Von: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]Im Auftrag von Christian
Paul B. Cosinas
Gesendet: Donnerstag, 20. Oktober 2005 10:44
An: pgsql-performance@postgresql.org
Betreff: [PERFORM] Deleting Records
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-10-20 08:59 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-20 08:43 Deleting Records Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-20 08:49 ` Csaba Nagy <nagy@ecircle-ag.com>
2005-10-20 08:52 ` Matteo Beccati <php@beccati.com>
2005-10-20 08:57 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-10-20 08:59 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-10-20 08:50 Re: Deleting Records Nörder-Tuitje, Marcus <noerder-tuitje@technology.de>
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