Re: Strange Create Index behaviour

Gary Doades <gpd(at)gpdnet(dot)co(dot)uk> writes:
> Platform: FreeBSD 6.0, Postgresql 8.1.2 compiled from the ports collection.

> If the column that is having the index created has a certain
> distribution of values then create index takes a very long time. If the
> data values (integer in this case) a fairly evenly distributed then
> create index is very quick, if the data values are all the same it is
> very quick. I discovered that in the slow cases the column had
> approximately half the values as zero and the rest fairly spread out.

Interesting. I tried your test script and got fairly close times
for all the cases on two different machines:
old HPUX machine: shortest 5800 msec, longest 7960 msec
new Fedora 4 machine: shortest 461 msec, longest 608 msec
(the HPUX machine was doing other stuff at the same time, so some
of its variation is probably only noise).

So what this looks like to me is a corner case that FreeBSD's qsort
fails to handle well.

You might try forcing Postgres to use our private copy of qsort, as we
do on Solaris for similar reasons. (The easy way to do this by hand
is to configure as normal, then alter the LIBOBJS setting in
src/Makefile.global to add "qsort.o", then proceed with normal build.)
However, I think that our private copy is descended from *BSD sources,
so it might have the same failure mode. It'd be worth finding out.

> The final interesting thing is that as I increase shared buffers to 2000
> or 3000 the problem gets *worse*

shared_buffers is unlikely to impact index build time noticeably in
recent PG releases. maintenance_work_mem would affect it a lot, though.
What setting were you using for that?

Can anyone else try these test cases on other platforms?

regards, tom lane