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