Re: reducing random_page_cost from 4 to 2 to force index scan

> I guess maybe the reason why it didn't matter for the OP is that - if
> the size of the index page in pages is smaller than the pro-rated
> fraction of effective_cache_size allowed to the index - then the exact
> value doesn't affect the answer.
>
> I apparently need to study this code more.

FWIW: random_page_cost is meant to be the ratio between the cost of
looking up a single row as and index lookup, and the cost of looking up
that same row as part of a larger sequential scan. For specific
storage, that coefficient should be roughly the same regardless of the
table size. So if your plan for optimization involves manipulating RPC
for anything other than a change of storage, you're Doing It Wrong.

Instead, we should be fixing the formulas these are based on and leaving
RPC alone.

For any data page, there are actually four costs associated with each
tuple lookup, per:

in-memory/seq | on disk/seq
----------------+----------------
in-memory/random| on disk/random

(yes, there's actually more for bitmapscan etc. but the example holds)

For any given tuple lookup, then, you can assign a cost based on where
you think that tuple falls in that quadrant map. Since this is all
probability-based, you'd be assigning a cost as a mixed % of in-memory
and on-disk costs. Improvements in accuracy of this formula would come
through improvements in accuracy in predicting if a particular data page
will be in memory.

This is what the combination of random_page_cost and
effective_cache_size ought to supply, but I don't think it does, quite.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com