Subject: Re: hash index improving v3
From: Simon Riggs <simon@2ndQuadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "Jonah H. Harris" <jonah.harris@gmail.com>,
	Alex Hunsaker <badalex@gmail.com>, Kenneth Marshall <ktm@rice.edu>,
	Xiao Meng <mx.cogito@gmail.com>, Zdenek Kotala <Zdenek.Kotala@sun.com>,
	pgsql-patches@postgresql.org
In-Reply-To: <18049.1222145316@sss.pgh.pa.us>
References: <34d269d40809052249h59750f83v4b19a50d0555580@mail.gmail.com>
	<34d269d40809091823t204e252fl246443be71aed9ff@mail.gmail.com>
	<20080910130401.GG6714@it.is.rice.edu>
	<34d269d40809102049y2599a381kf409c0b5f18b6170@mail.gmail.com>
	<34d269d40809102117g470c4d25l55be7898fc43fc40@mail.gmail.com>
	<20080911152437.GM6714@it.is.rice.edu>
	<34d269d40809111951s7bac44cfk5dc9244693dd777c@mail.gmail.com>
	<20080912142921.GB26773@it.is.rice.edu> <7246.1221445012@sss.pgh.pa.us>
	<34d269d40809222025k2b2252dft89db471649c8eef8@mail.gmail.com>
	<36e682920809222030k49293e85jc57429296c5d2d76@mail.gmail.com>
	<17323.1222142759@sss.pgh.pa.us>
	<1222144262.4445.265.camel@ebony.2ndQuadrant>
	<18049.1222145316@sss.pgh.pa.us>
Content-Type: text/plain
Date: Tue, 23 Sep 2008 06:15:36 +0100
Message-Id: <1222146936.4445.290.camel@ebony.2ndQuadrant>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Tue, 2008-09-23 at 00:48 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > I wasn't very happy with effective_cache_size and not happy with
> > shared_buffers either. If building hash indexes is memory critical then
> > we just need to say so and encourage others to set memory use correctly.
> > People are already aware that maintenance_work_mem needs to be increased
> > for large index builds and we will confuse people if we ignore that and
> > use another parameter instead.
> 
> I think you've got this completely backwards.  The general theory about
> maintenance_work_mem is "set it as large as you can stand it".  The
> issue at hand here is that the crossover point for hash index sort
> building seems to be a good deal less than all-the-memory-you-have.

There's an optimal point for btree builds using sorts that is a good
deal less also, so I don't get that.

Plus, if you give it all-the-memory-you-have there won't be anything
left for anything else. You might set it that high for an empty database
load but you don't set it that high in production unless you want to see
swapping when you create large indexes.

maintenance_work_mem is already used for 3 separate operations that bear
little resemblance to each other. If it's appropriate for all of those
then its appropriate for this usage also. Doing it otherwise is going to
mean more than 50% of people do the wrong thing, which would be a shame
because what's been done looks very cool.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support