From: Simon Riggs <simon@2ndQuadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jonah H. Harris <jonah.harris@gmail.com>
Cc: Alex Hunsaker <badalex@gmail.com>
Cc: Kenneth Marshall <ktm@rice.edu>
Cc: Xiao Meng <mx.cogito@gmail.com>
Cc: Zdenek Kotala <Zdenek.Kotala@sun.com>
Cc: pgsql-patches@postgresql.org
Subject: Re: hash index improving v3
Date: Tue, 23 Sep 2008 06:15:36 +0100
Message-ID: <1222146936.4445.290.camel@ebony.2ndQuadrant> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: simon@2ndQuadrant.com, tgl@sss.pgh.pa.us, jonah.harris@gmail.com, badalex@gmail.com, ktm@rice.edu, mx.cogito@gmail.com, Zdenek.Kotala@sun.com
Subject: Re: hash index improving v3
In-Reply-To: <1222146936.4445.290.camel@ebony.2ndQuadrant>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox