I created a table with two columns: an id SERIAL (primary key) and a
text (not null), and then added a unique index on the text field.
Then I ran the following query (with a huge work_mem - 20GB):
insert into tableA (text_field) select distinct other_text_field from
some_huge_set_of_tables
After 36 hours it had only written 3 GB (determined by looking at what
files it was writing to).
I started over with a TRUNCATE, and then removed the index and tried again.
This time it took 3807270.780 ms (a bit over an hour).
Total number of records: approx 227 million, comprising 16GB of storage.
Why the huge discrepancy?
--
Jon