DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:from:date:message-id:subject:to:content-type;
	b=h3XK8KvDavEyy6VnjfpDUADpgYPPZtFTC7DKoKNs4DIHWb8jfAme2exJR0Q+3vKPNk
	BPxpks2CScmxfdqtIOQ6QidqQuT4Qn0JVrvD2a797ddHQ2AXHUd+jyGWi9MxTJH1OyQk
	UtBH3ccAffEcXgU3E8p7quQZUXKHmmsVY8+oQ=
MIME-Version: 1.0
From: Josh <slushie@gmail.com>
Date: Thu, 10 Feb 2011 11:57:23 -0600
Message-ID: <AANLkTim0z576Axfm4QWTTg_RXbdMgpppWC_rTF5ZzRoA@mail.gmail.com>
Subject: DELETE FROM takes forever
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

Hi

I'm trying to do a DELETE FROM on my large table (about 800 million
rows) based on the contents of another, moderately large table (about
110 million rows). The command I'm using is:

DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records);

This process ran for about two weeks before I decided to stop it -- it
was dragging down the DB server. I can understand long-running
processes, but two weeks seems a bit much even for a big table.

Is this the best way to approach the problem? Is there a better way?

Some background: The server is version 8.3, running nothing but Pg.
The 'records' table has 'id' as its primary key, and one other index
on another column. The table is referenced by just about every other
table in my DB (about 15 other tables) via foreign key constraints,
which I don't want to break (which is why I'm not just recreating the
table rather than deleting rows). Most of the dependent tables have ON
DELETE CASCADE. The 'unique_records' table is a temp table I got via
something like: SELECT DISTINCT (other_column) id INTO unique_records
FROM records


Thanks very much!

Josh Leder