Re: Auto-tuning work_mem and maintenance_work_mem

On Mon, Mar 10, 2014 at 3:16 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> 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?

If the next tuple is already hinted and not compressed or toasted, I would
completely believe that. In fact, unless the operator is integer or dp, I
would say it is less than 2 times as expensive. If it is a text operator
and the collation is not "C" or "POSIX", then moving to the next tuple is
likely less expensive than a single operator evaluation.

If your tuples are updated nearly as often as queried, the hint resolution
could be a big cost. But in that case, probably the contention would be a
bigger issue than the pure CPU cost.

I don't know how compression and toast would affect the times. Are your
tables heavily toasted?

If top down measurements and bottom up measurements aren't giving the same
results, then what is going on? We know and document how caching needs to
be baked into the page costs parameters. What unknown thing is throwing
off the cpu costs?

> 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.
>

Have you ever tried lowering the other two cpu cost parameters instead?
That would be the more definitive indication that the benefit is not
coming just by moving the io costs closer to the cpu costs

Cheers,

Jeff