Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Tue, Nov 3, 2009 at 7:13 AM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Grant Masan <grant(dot)massan(at)gmail(dot)com> wrote:
>>
>>
>>> cpu_tuple_cost = 0.0030
>>> cpu_index_tuple_cost = 0.0010
>>> cpu_operator_cost = 0.0005
>>
>> Why did you make these adjustments? I usually have to change the
>> ratio between page and cpu costs toward the other direction.
>
> Is that because the database is mostly cached in memory? If I take
> the documented descriptions of the costs parameters at face value, I
> find that cpu_tuple_cost should be even lower yet.
Right, the optimizer doesn't model caching effects very well, so I
find that in practice I have to fudge these from their putative
meanings to allow for typical caching. Even with only a small
fraction of the database cached, the heavily accessed indexes tend to
be fairly well cached, so overall performance improves markedly by
dropping random_page_cost to about 2, even in our lowest-percentage-
cached databases.
I've occasionally tried using the defaults for that GUC, which has
always resulted in user complaints about unacceptable performance of
important queries. While I tend to reduce the random_page_cost and
seq_page_cost to tweak things, raising the cpu_*_cost settings would
accomplish the same thing, so reducing them as show above would tend
to push things into sequential scans where indexed access might be
faster.
-Kevin