From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gary Doades <gpd@gpdnet.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Strange Create Index behaviour
Date: Wed, 15 Feb 2006 15:56:08 -0500
Message-ID: <19510.1140036968@sss.pgh.pa.us> (raw)
In-Reply-To: <43F38867.6010701@gpdnet.co.uk>
References: <43F38867.6010701@gpdnet.co.uk>
Gary Doades <gpd@gpdnet.co.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
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: tgl@sss.pgh.pa.us, gpd@gpdnet.co.uk
Subject: Re: Strange Create Index behaviour
In-Reply-To: <19510.1140036968@sss.pgh.pa.us>
* 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