Skip site navigation (1) Skip section navigation (2)

Re: Strange Create Index behaviour

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Gary Doades <gpd(at)gpdnet(dot)co(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Strange Create Index behaviour
Date: 2006-02-16 01:52:09
Message-ID: 1140054729.12131.225.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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




In response to

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2006-02-16 01:52:46
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour)
Previous:From: Dann CorbitDate: 2006-02-16 01:37:58
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour)

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2006-02-16 01:52:46
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour)
Previous:From: Tom LaneDate: 2006-02-16 01:50:35
Subject: Re: Generating config stuff from single source

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group