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 BDE8F9DCBBB;
	Wed, 15 Feb 2006 20:17:00 -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 62497-09; Wed, 15 Feb 2006 20:17:03 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 5A69D9DCB9E;
	Wed, 15 Feb 2006 20:16:58 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1G0H0ZX021153;
	Wed, 15 Feb 2006 19:17:00 -0500 (EST)
To: Gary Doades <gpd@gpdnet.co.uk>
cc: pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index behaviour) 
In-reply-to: <43F3BF72.6000202@gpdnet.co.uk> 
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> <43F3BF72.6000202@gpdnet.co.uk>
Comments: In-reply-to Gary Doades <gpd@gpdnet.co.uk>
	message dated "Wed, 15 Feb 2006 23:55:30 +0000"
Date: Wed, 15 Feb 2006 19:17:00 -0500
Message-ID: <21152.1140049020@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.11 required=5 tests=[AWL=0.110]
X-Spam-Score: 0.11
X-Spam-Level: 
X-Archive-Number: 200602/592
X-Sequence-Number: 79730

Gary Doades <gpd@gpdnet.co.uk> writes:
> 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.

Actually... we only use qsort when we have a sorting problem that fits
within the allowed sort memory.  The external-sort logic doesn't go
through that code at all.  So all the analysis we just did on your test
case doesn't necessarily apply to sort problems that are too large for
the sort_mem setting.

The test case would be sorting 200000 index entries, which'd probably
occupy at least 24 bytes apiece of sort memory, so probably about 5 meg.
A problem 20 times that size would definitely not fit in the default
16MB maintenance_work_mem.  Were you using a large value of
maintenance_work_mem for your restore?

			regards, tom lane