From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [HACKERS] A Better External Sort?
Date: Mon, 3 Oct 2005 13:40:29 -0700
User-Agent: KMail/1.8
Cc: "Jeffrey W. Baker" <jwbaker@acm.org>,
	Ron Peacetree <rjpeace@earthlink.net>, pgsql-hackers@postgresql.org,
	pgsql-performance@postgresql.org
References: 
 <21402654.1127923414088.JavaMail.root@elwamui-polski.atl.sa.earthlink.net>
	<433C1C2D.7090908@agliodbs.com> <8643.1128181455@sss.pgh.pa.us>
In-Reply-To: <8643.1128181455@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200510031340.29376.josh@agliodbs.com>

Tom,

> Raising work_mem to a gig should result in about five runs, needing only
> one pass, which is really going to be as good as it gets.  If you could
> not see any difference then I see little hope for the idea that reducing
> the number of merge passes will help.

Right.  It *should have*, but didn't seem to.    Example of a simple sort 
test of 100 million random-number records

1M   3294 seconds
  16M   1107 seconds
  256M   1209 seconds
  512M   1174 seconds
  512M with 'not null' for column that is indexed  1168 seconds

> Umm ... you were raising maintenance_work_mem, I trust, not work_mem?

Yes.

>
> We really need to get some hard data about what's going on here.  The
> sort code doesn't report any internal statistics at the moment, but it
> would not be hard to whack together a patch that reports useful info
> in the form of NOTICE messages or some such.

Yeah, I'll do this as soon as the patch is finished.   Always useful to 
gear up the old TPC-H.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco