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

On May 16, 2011, at 10:46 AM, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mon, May 16, 2011 at 12:49 AM, Jesper Krogh <jesper@krogh.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.
>
>> Tables can have hot spots, too.  Consider a table that holds calendar
>> reservations.  Reservations can be inserted, updated, deleted.  But
>> typically, the most recent data will be what is most actively
>> modified, and the older data will be relatively more (though not
>> completely) static, and less frequently accessed.  Such examples are
>> common in many real-world applications.
>
> Yes.  I'm not convinced that measuring the fraction of a table or index
> that's in cache is really going to help us much.  Historical cache hit
> rates might be useful, but only to the extent that the incoming query
> has a similar access pattern to those in the (recent?) past.  It's not
> an easy problem.
>
> I almost wonder if we should not try to measure this at all, but instead
> let the DBA set a per-table or per-index number to use, analogous to the
> override we added recently for column n-distinct statistics ...

I think the challenge there would be how to define the scope of the hot-spot. Is it the last X pages? Last X serial
values?Something like correlation? 

Hmm... it would be interesting if we had average relation access times for each stats bucket on a per-column basis;
thatwould give the planner a better idea of how much IO overhead there would be for a given WHERE clause. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net