Date: Thu, 30 Mar 2006 21:45:31 +0100
From: Gavin Hamill <gdh@laterooms.com>
To: pgsql-performance@postgresql.org
Subject: Re: CREATE INDEX rather sluggish
Message-Id: <20060330214531.ce6620e1.gdh@laterooms.com>
In-Reply-To: <1143738524.13549.103.camel@localhost.localdomain>
References: <442B9625.1060408@laterooms.com>
	<1143738524.13549.103.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Thu, 30 Mar 2006 18:08:44 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:

Hello again Simon :)

> The index build time varies according to the number and type of the
> datatypes, as well as the distribution of values in the table. As well
> as the number of rows in the table.
> 
> Note the x10 factor to index AreaID (integer) v KeywordType (vchar(20))

Fair enough. :) Is there much of a performance increase by using fixed-length character fields instead of varchars?

> Try trace_sort = on and then rerun the index builds to see what's
> happening there. We've speeded sort up by about 2.5 times in the current
> development version, but it does just run in single threaded mode so
> your 8 CPUs aren't helping there.

Yum - I look forward to the 8.2 release =) 
 
> Looks like you might be just over the maintenance_work_mem limit for the
> last index builds. You can try doubling maintenance_work_mem.

You were right - needed ~370MB ... I'm happy to alloc 1GB to allow for db growth..

> The extended runtime for KeywordType is interesting in comparison to
> LowerText, which on the face of it is a longer column. My guess would be
> that LowerText is fairly unique and sorts quickly, whereas KeywordType
> is fairly non-unique with a high average row length that require
> complete string comparison before deciding it is actually the same
> value.