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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2006-02-16 01:52:46 | Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour) |
Previous Message | Tom Lane | 2006-02-16 01:50:35 | Re: Generating config stuff from single source |
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2006-02-16 01:52:46 | Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour) |
Previous Message | Dann Corbit | 2006-02-16 01:37:58 | Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour) |