Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group