thread-index: AcgSX5sv8RTjsMC1R1WGceI66YOBLA==
Message-ID: <4718C41F.8090202@enterprisedb.com>
Content-Class: urn:content-classes:message
Importance: normal
Date: Fri, 19 Oct 2007 15:50:07 +0100
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: "Yinan Li" <yinanli@cse.ust.hk>
Cc: "Postgresql Performance" <pgsql-performance@postgresql.org>
Subject: Re: how to improve the performance of creating index
References: <01c601c81247$3b8c5650$b2a502f0$@ust.hk>
	<47189D0F.6090707@enterprisedb.com>
	<01cb01c8125e$011183d0$03348b70$@ust.hk>
In-Reply-To: <01cb01c8125e$011183d0$03348b70$@ust.hk>
Content-Type: text/plain;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

Please keep the list cc'd so that others can help.

Yinan Li wrote:
> What version of Postgres are you using?
> 8.2.4
> How much RAM does the box have?
> 2G
> How big is the table? 
> 256M tuples, each tuple contains 2 integers.
> How long does the index build take?
> About 2 hours
> What kind of an I/O system do you have?
> A SATA disk (7200 rpm)

2 hours does sound like a very long time. With a table like that, we're
talking about ~7-9 GB worth of data if I did the math right.

You could try lowering shared_buffers to something like 50 MB while you
build the index, to leave more RAM available for OS caching.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com