Re: Auto-tuning work_mem and maintenance_work_mem

Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-02-16 21:26:47 -0500, Robert Haas wrote:

>> I don't really know about cpu_tuple_cost.  Kevin's often
>> advocated raising it, but I haven't heard anyone else advocate
>> for that. I think we need data points from more people to know
>> whether or not that's a good idea in general.
>
> FWIW It's a good idea in my experience.

This is more about the balance among the various cpu_* costs than
the balance between cpu_* costs and the *_page costs.  I usually
need to adjust the page costs, too; and given how heavily cached
many machines are, I'm usually moving them down.  But if you think
about the work involved in moving to a new tuple, do you really
think it's only twice the cost of moving to a new index entry on an
index scan?  Or only four times as expensive as executing an
average operator function?  In my experience setting cpu_tuple_cost
higher tends to better model costs, and prevent CPU-sucking scans
of large numbers of rows.

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.

FWIW, even though I'm repeating something I've mentioned before,
whenever raising this setting did help, 0.03 was high enough to see
the benefit.  Several times I have also tried 0.05 just to test
whether I was wandering near a tipping point for a bad choice from
this.  I have never had 0.05 produce plans noticeably better or
worse than 0.03.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company