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

Re: Deleting Records

From: Nörder-Tuitje, Marcus <noerder-tuitje(at)technology(dot)de>
To: "Christian Paul B(dot) Cosinas" <cpc(at)cybees(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Deleting Records
Date: 2005-10-20 08:50:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
what about firing a 

DELETE FROM qc_session S 
                       FROM item_qc_doer i
                      WHERE i.item_id = s.item_id);


DELETE FROM item_qc_doer S 
                       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 ;-)


-----Ursprüngliche Nachricht-----
Von: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org]Im Auftrag von Christian
Paul B. Cosinas
Gesendet: Donnerstag, 20. Oktober 2005 10:44
An: pgsql-performance(at)postgresql(dot)org
Betreff: [PERFORM] Deleting Records


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;

Item_qc_oder table contains 22,000 records.

I choose Polesoft Lockspam to fight spam, and you?    

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

pgsql-performance by date

Next:From: Matteo BeccatiDate: 2005-10-20 08:52:29
Subject: Re: Deleting Records
Previous:From: Csaba NagyDate: 2005-10-20 08:49:04
Subject: Re: Deleting Records

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