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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
