Re: CREATE INDEX rather sluggish

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.

From looking at a few samples of the millions of rows it seems that it's actually KeywordType that's more unique -
LowerTextis simply an lowercase representation of the name of this search-keyword, so it's much less unique. Fun stuff
:)

> You might want to try using codes rather than textual KeywordTypes.

That makes sense - I can't get a grip on the data in KeywordType at the moment .. many are more obvious like 'RGN'
'AREA''MKT' 'LK' for Region, Area, Market and Lake, but many other rows have '1'. 

> You might try using partial indexes also, along the lines of
>
> CREATE INDEX ix_keyword_type ON "Keyword" USING btree ("KeywordType") WHERE KeywordType IS NOT NULL;

Well, each row does have a KeywordType, so no row has a NULL entry...

> Best Regards, Simon Riggs

Cheers :)
Gavin.