Re: planner costs in "warm cache" tests

It is still best to have random_page_cost to be slightly larger (~50%) than sequential_page_cost, because even when entirely in RAM, sequential reads are faster than random reads. Today's CPU's do memory prefetching on sequential access. Perhaps try something like 0.3 and 0.2, or half that. You still don't want it to gratuitously scan a lot of RAM -- reading a page is not free and can kick out other pages from shared_buffers.

On May 31, 2010, at 12:55 PM, Tom Lane wrote:

> Jesper Krogh <jesper(at)krogh(dot)cc> writes:
>> On 2010-05-30 20:34, Tom Lane wrote:
>>> Well, hmm, I really doubt that that represents reality either. A page
>>> access is by no means "free" even when the page is already in cache.
>>> I don't recall anyone suggesting that you set these numbers to less
>>> than perhaps 0.01.
>>>
>> Thank you for the prompt response. Is it a "false assumption" that the
>> cost should in some metric between different plans be a measurement
>> of actual run-time in a dead-disk run?
>
> Well, the default cost parameters (seq_page_cost=1, random_page_cost=4)
> are intended to model the non-cached state where most page fetches
> actually do require a disk access. They are definitely too large
> relative to the cpu_xxx_cost parameters when you have a fully-cached
> database, but what I've seen people recommending for that condition
> is to set them both to the same value in the vicinity of 0.1 or 0.01
> or so. If it's only mostly cached you might try intermediate settings.
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance