Re: CREATE INDEX rather sluggish

[Gavin Hamill <gdh(at)laterooms(dot)com>]

On Thu, 30 Mar 2006 18:08:44 +0100
Simon Riggs <simon(at)2ndquadrant(dot)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.