Index creation and maintenance_work_mem

From: "Francisco Reyes" <lists(at)stringsutils(dot)com>
To: " Pgsql General list " <pgsql-general(at)postgresql(dot)org>
Subject: Index creation and maintenance_work_mem
Date: 2008-07-29 18:06:53
Message-ID: 536821bd78f6794963f49d1712216d3d@stringsutils.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Besides maintenance_work_mem, what else can be changed to improve index
creation?

I just did two tests. One with maintenance_work_mem=128MB and another with
maintenance_work_mem=1GB. Out of 3 single column index, 2 took slightly
longer with the higher value and a third took almost the same.
12GB of ram in the machine. Redhat 4 revision 6. Postgesql 8.3.3.
temp_buffers = 64MB
work_mem = 96MB
checkpoint_segments = 256
checkpoint_timeout = 10min
Indexing 60 million rows.

Tests run from a script and nothing else was running on the machine during
the tests.

maintenance_work_mem = 128MB
CREATE INDEX
Time: 449626.651 ms 7.4 minutes

CREATE INDEX
Time: 313004.025 ms 5.2 minutes

CREATE INDEX
Time: 3077382.941 ms 51.2 minutes

maintenance_work_mem = 1GB
CREATE INDEX
Time: 497358.902 ms 8.2 minutes

CREATE INDEX
Time: 312316.953 ms 5.2 minutes

CREATE INDEX
Time: 3236472.630 ms 53.9

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Richardson 2008-07-29 18:07:46 How do I convert a timestamp with time zone to local time?
Previous Message Tom Lane 2008-07-29 15:52:01 Re: interesting trigger behaviour in 8.3