From: Simon Riggs <simon@2ndquadrant.com>
To: Gary Doades <gpd@gpdnet.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange Create Index behaviour
Date: Thu, 16 Feb 2006 01:52:09 +0000
Message-ID: <1140054729.12131.225.camel@localhost.localdomain> (raw)
In-Reply-To: <43F38867.6010701@gpdnet.co.uk>
References: <43F38867.6010701@gpdnet.co.uk>
On Wed, 2006-02-15 at 20:00 +0000, Gary Doades wrote:
> I have put together a test case that demonstrates the problem (see
> below). I create a simple table, as close in structure to one of my
> problem tables and populate an integer column with 100,000 zeros follow
> by 100,000 random integers between 0 and 100,000. Then create an index
> on this column. I then drop the table and repeat. The create index
> should take around 1-2 seconds. A fair proportion of the time it takes
> 50 seconds!!!
>
> If I fill the same row with all random data the create index always
> takes a second or two. If I fill the column with all zeros everything is
> still OK.
Aside from the importance of investigating sort behaviour, have you
tried to build a partial index WHERE col > 0 ? That way you wouldn't
even be indexing the zeros.
Best Regards, Simon Riggs
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: simon@2ndquadrant.com, gpd@gpdnet.co.uk
Subject: Re: Strange Create Index behaviour
In-Reply-To: <1140054729.12131.225.camel@localhost.localdomain>
* 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