Re: Auto-tuning work_mem and maintenance_work_mem

On 03/10/2014 03:16 PM, Kevin Grittner wrote:
> I only have anecdotal evidence, though.  I have seen it help dozens
> of times, and have yet to see it hurt.  That said, most people on
> this list are probably capable of engineering a benchmark which
> will show whichever result they would prefer.  I would prefer to
> hear about other data points based on field experience with
> production systems.  I haven't offered the trivial patch because
> when I've raised the point before, there didn't seem to be anyone
> else who had the same experience.  It's good to hear that Andres
> has seen this, too.

The problem with cpu_tuple_cost is that it's used in several places by
the planner and makes it hard to model what the effect of any change
would be.  If we had a good general benchmark which actually gave the
query planner a workout, we could come up with some reasonable default
settings, but right now we can't.

Back in 2004-2006 era, when CPU speeds had leapfrogged ahead of disk
speeds (which were largely unchanged from 2000), I was routinely
*lowering* cpu_tuple_cost (and cpu_index_tuple_cost) to get better
plans.  This was baked into early versions of Greenplum for that reason.

So I'm not saying that we shouldn't change the default for
cpu_tuple_cost.  I'm saying that we currently don't have enough
information on *when* and *how much* to change it.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com