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