On 2011-02-10, Josh slus...@gmail.com wrote:
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
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Josh
Sent: Thursday, February 10, 2011 11:57 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] DELETE FROM takes forever
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
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
On Thu, Feb 10, 2011 at 9:57 AM, Josh slus...@gmail.com wrote:
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
Josh slus...@gmail.com writes:
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
Are your IDs (in both tables) a subject of index?
If so, analyze tables and indexes. If not, create an index for each ID -
that may help.
Post an explain plan of the query as well. I guess there is full scan
instead of index scan, thus running over and over 800 rows vs 110mln
rows may take a
Many of the tables do not have indexes on the FK, though a couple of
the biggest ones do. It does seem worth the time to put an index on
each of these tables, considering the few hundred hours I'm already
spending on the DELETE.
I've started the EXPLAIN ANALYZE but it will take a while, no doubt.
slus...@gmail.com (Josh) writes:
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