From: Gavin Hamill <gdh@laterooms.com>
To: pgsql-performance@postgresql.org
Subject: Re: CREATE INDEX rather sluggish
Date: Thu, 30 Mar 2006 21:45:31 +0100
Message-ID: <20060330214531.ce6620e1.gdh@laterooms.com> (raw)
In-Reply-To: <1143738524.13549.103.camel@localhost.localdomain>
References: <442B9625.1060408@laterooms.com>
<1143738524.13549.103.camel@localhost.localdomain>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gdh@laterooms.com
Subject: Re: CREATE INDEX rather sluggish
In-Reply-To: <20060330214531.ce6620e1.gdh@laterooms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox