Re: hash index improving v3

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>, Alex Hunsaker <badalex(at)gmail(dot)com>, Kenneth Marshall <ktm(at)rice(dot)edu>, Xiao Meng <mx(dot)cogito(at)gmail(dot)com>, Zdenek Kotala <Zdenek(dot)Kotala(at)sun(dot)com>, pgsql-patches(at)postgresql(dot)org
Subject: Re: hash index improving v3
Date: 2008-09-23 05:15:36
Message-ID: 1222146936.4445.290.camel@ebony.2ndQuadrant
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

On Tue, 2008-09-23 at 00:48 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)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
PostgreSQL Training, Services and Support

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2008-09-23 05:40:10 Re: parallel pg_restore
Previous Message Tom Lane 2008-09-23 04:48:36 Re: hash index improving v3

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2008-09-23 12:16:34 Re: hash index improving v3
Previous Message Tom Lane 2008-09-23 04:48:36 Re: hash index improving v3