X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id D29BD9DC89F;
	Wed, 15 Feb 2006 19:57:32 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 58307-02; Wed, 15 Feb 2006 19:57:35 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from server.gpdnet.co.uk (gpdnet.plus.com [212.56.100.243])
	by postgresql.org (Postfix) with ESMTP id 937819DC800;
	Wed, 15 Feb 2006 19:57:30 -0400 (AST)
Received: from [192.168.1.2] (unknown [192.168.1.2])
	by server.gpdnet.co.uk (Postfix) with ESMTP id 6E4A33AAB50;
	Thu, 16 Feb 2006 00:08:14 +0000 (GMT)
Message-ID: <43F3BF72.6000202@gpdnet.co.uk>
Date: Wed, 15 Feb 2006 23:55:30 +0000
From: Gary Doades <gpd@gpdnet.co.uk>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org,  pgsql-hackers@postgresql.org
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour)
References: <43F38867.6010701@gpdnet.co.uk> <19510.1140036968@sss.pgh.pa.us>
	<19779.1140038874@sss.pgh.pa.us> <43F39E53.1020009@gpdnet.co.uk>
	<20781.1140046109@sss.pgh.pa.us>
In-Reply-To: <20781.1140046109@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120]
X-Spam-Score: 0.12
X-Spam-Level: 
X-Archive-Number: 200602/590
X-Sequence-Number: 79728

Tom Lane wrote:
> For some reason I hadn't immediately twigged to the fact that your test
> script is just N repetitions of the exact same structure with random data.
> So it's not so surprising that you get random variations in behavior
> with different test data sets.
> 
  > It seems clear that our qsort.c is doing a pretty awful job of picking
> qsort pivots, while glibc is mostly managing not to make that mistake.
> I haven't looked at the glibc code yet to see what they are doing
> differently.
> 
> I'd say this puts a considerable damper on my enthusiasm for using our
> qsort all the time, as was recently debated in this thread:
> http://archives.postgresql.org/pgsql-hackers/2005-12/msg00610.php
> We need to fix our qsort.c before pushing ahead with that idea.

[snip]

> Time: 28314.182 ms
> Time: 29400.278 ms
> Time: 34142.534 ms

Ouch! That confirms my problem. I generated the random test case because 
it was easier than including the dump of my tables, but you can 
appreciate that tables 20 times the size are basically crippled when it 
comes to creating an index on them.

Examining the dump and the associated times during restore it looks like 
I have 7 tables with this approximate distribution, thus the 
ridiculously long restore time. Better not re-index soon!

Is this likely to hit me in a random fashion during normal operation, 
joins, sorts, order by for example?

So the options are:
1) Fix the included qsort.c code and use that
2) Get FreeBSD to fix their qsort code
3) Both

I guess that 1 is the real solution in case anyone else's qsort is 
broken in the same way. Then at least you *could* use it all the time :)

Regards,
Gary.