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: <17323.1222142759@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>
Content-Type: text/plain
Date: Tue, 23 Sep 2008 05:31:02 +0100
Message-Id: <1222144262.4445.265.camel@ebony.2ndQuadrant>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Tue, 2008-09-23 at 00:05 -0400, Tom Lane wrote:
> "Jonah H. Harris" <jonah.harris@gmail.com> writes:
> > On Mon, Sep 22, 2008 at 11:25 PM, Alex Hunsaker <badalex@gmail.com> wrote:
> >> On Sun, Sep 14, 2008 at 8:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>> I'm considering changing hashbuild to switch over at shared_buffers instead
> >>> of effective_cache_size --- any thoughts about that?
> >> 
> >> Switching to shared_buffers gets my vote, on my test table with
> >> 50,000,000 rows it takes about 8 minutes to create an index using the
> >> default effective_cache_size.  With effective_cache_size set to 6GB
> >> (machine has 8GB) its still going an hour later.
> 
> > Agreed.  I think using shared_buffers as a cutoff is a much better idea as well.
> 
> Already done in CVS a week or so back, but thanks for following up with
> some confirmation.

I think nobody noticed that change, or the discussion.

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. At the very least, a controllable
parameter is the only appropriate choice for production systems, not one
that cannot be changed without restart. It would also throw away any
chance of having pg_restore of hash indexes run in parallel, since it
will not be able to control memory usage. Setting maintenance_work_mem
higher than shared buffers is easily possible now and we should just use
that rather than try to prejudge how people will and can configure their
systems. If maintenance_work_mem default is too low, lets increase it.

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