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

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Sun, May 15, 2011 at 9:49 PM, Jesper Krogh <jesper(at)krogh(dot)cc> wrote:
>> Ok, it may not work as well with index'es, since having 1% in cache may very
>> well mean that 90% of all requested blocks are there.. for tables in should
>> be more trivial.

> Why would the index have a meaningful hot-spot unless the underlying
> table had one as well? (Of course the root block will be a hot-spot,
> but certainly not 90% of all requests)

The accesses to an index are far more likely to be clustered than the
accesses to the underlying table, because the index is organized in a
way that's application-meaningful and the table not so much. Continuing
the earlier example of a timestamp column, accesses might preferentially
hit near the right end of the index while the underlying rows are all
over the table.

IOW, hot spots measured at the row level and hot spots measured at the
page level could very easily be different between table and index.

regards, tom lane