Thread: *really* bad insert performance on table with unique index

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

On Thu, Feb 2, 2012 at 9:28 AM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> 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

I bet the distinct is being implemented by a hashAggregate.  So then
you are inserting the records in a random order, causing the index to
have terrible locality of reference.

Try adding "order by other_text_field" to the select.  Or don't create
the index until afterwards

>
> 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?

Maintaining indices when rows are inserted in a random order generates
a huge amount of scattered I/O.

Cheers,

Jeff