From: Josh <slushie@gmail.com>
To: pgsql-sql@postgresql.org
Subject: DELETE FROM takes forever
Date: Thu, 10 Feb 2011 11:57:23 -0600
Message-ID: <AANLkTim0z576Axfm4QWTTg_RXbdMgpppWC_rTF5ZzRoA@mail.gmail.com> (raw)
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
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-sql@postgresql.org
Cc: slushie@gmail.com
Subject: Re: DELETE FROM takes forever
In-Reply-To: <AANLkTim0z576Axfm4QWTTg_RXbdMgpppWC_rTF5ZzRoA@mail.gmail.com>
* 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