Help, 400 million rows, query slow, I stopped after 5 days - Mailing list pgsql-novice

From Christian Hofmann
Subject Help, 400 million rows, query slow, I stopped after 5 days
Date
Msg-id 00b801c625b2$30d04600$9000a8c0@taschenrechner
Whole thread Raw
Responses Re: Help, 400 million rows, query slow, I stopped after 5 days
List pgsql-novice
Hello,

I have a little table with 400,000,000 rows of text (about 40 chars each).
I want to delete values that are exisiting more than two times.

So I created a new table (table2), the same than the first, but with an
unique index.
Then i put in the following query:

INSERT INTO table2 (my_col) SELECT DISTINCT my_col from table1

But this query was so slow that I stopped it. I let it run for five days!

In the Server there is only 1 GB RAM. My_col is using a bitmap index.

Maybe I should creating a trigger on table2 that will check before inserting
if this value is already in the table2.


Or is it possible to just use the unique index in table2 and do something
like:

INSERT INTO table2 (my_col) SELECT my_col from table1

And ignoring there errors that are thrown because there are duplicate
values?

I hope you can help me,

Thank you,

Christian



pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: wrong objects order using pg_dump
Next
From: Oleg
Date:
Subject: Re: wrong objects order using pg_dump