Re: Forcing index scan on query produces 16x faster

On Wed, Mar 24, 2010 at 8:59 PM, Eger, Patrick <peger(at)automotive(dot)com> wrote:
> Ok, the wording is a bit unclear in the documentation as to whether it is the cost for an entire *page* of tuples, or actual tuples. So something like the following might give better results for a fully-cached DB?
>
> seq_page_cost = 1.0
> random_page_cost = 1.1 #even memory has random access costs, lack of readahead, TLB misses, etc
> cpu_tuple_cost = 1.0
> cpu_index_tuple_cost = 0.5
> cpu_operator_cost = 0.25
> effective_cache_size = 1000MB
> shared_buffers = 1000MB

Yeah, you can do it that way, by jacking up the cpu_tuple costs. I
prefer to lower the {random/seq}_page_cost values because it keeps the
cost values in the range I'm used to seeing, but it works out to the
same thing.

I am not sure that there is any benefit from making random_page_cost >
seq_page_cost on a fully cached database. What does readahead mean in
the context of cached data? The data isn't likely physically
contiguous in RAM, and I'm not sure it would matter much if it were.
Basically, what random_page_cost > seq_page_cost tends to do is
discourage the use of index scans in borderline cases, so you want to
benchmark it and figure out which way is faster and then tune
accordingly.

...Robert