On Thu, 30 Mar 2006 18:08:44 +0100 Simon Riggs <simon@xxxxxxxxxxxxxxx> 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.
