Subject: Re: Postgresql on an AMD64 machine
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>, pgsql-performance@postgresql.org
In-Reply-To: <26813.1118202633@sss.pgh.pa.us>
References: <1118080420.1644.23.camel@penguin1>
	<20050607202755.GA28361@surnet.cl> <18285.1118178689@sss.pgh.pa.us>
	<200506072005.47418.josh@agliodbs.com> <26813.1118202633@sss.pgh.pa.us>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Wed, 08 Jun 2005 07:16:14 +0100
Message-Id: <1118211374.3844.1617.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Tue, 2005-06-07 at 23:50 -0400, Tom Lane wrote:
> > Regarding 2GB memory allocation, though, we *could* really use support for 
> > work_mem and maintenance_mem of > 2GB.   
> 
> Again, let's see some evidence that it's worth putting effort into that.
> (Offhand it seems this is probably an easier fix than changing the
> shared-memory allocation code; but conventional wisdom is that really
> large values of work_mem are a bad idea, and I'm not sure I see the case
> for maintenance_work_mem above 2Gb either.)

We have strong evidence that an in-memory sort is better than an
external sort. And strong evidence that a hash-join/aggregate is faster
than a sort-merge or sort-aggregate.

What other evidence do you need?

The idea that work_mem is bad is a workload dependent thing. It assumes
that using the memory for other things is useful. That isn't the case
for apps with large tables, which just churn through memory with zero
gain.

In 8.2, I imagine a workload management feature that would limit the
allocation of work_mem and maintenance_work_mem, so that they can be
more safely allocated to very high values in production. That would open
the door to the use of very high work_mem values.

Best Regards, Simon Riggs