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

Craig Ringer wrote:
> AFAIK, mincore() is only useful for mmap()ed files and for finding out
> if it's safe to access certain blocks of memory w/o risking triggering
> heavy swapping.
>
> It doesn't provide any visibility into the OS's block device / file
> system caches; you can't ask it "how much of this file is cached in RAM"
> or "is this range of blocks in this file cached in RAM".
>

You should try out pgfincore if you think this can't be done!

> Even if you could, it's hard to see how an approach that relied on
> asking the OS through system calls about the cache state when planning
> every query could be fast enough to be viable.
>

You can't do it in real-time. You don't necessarily want that to even
if it were possible; too many possibilities for nasty feedback loops
where you always favor using some marginal index that happens to be in
memory, and therefore never page in things that would be faster once
they're read. The only reasonable implementation that avoids completely
unstable plans is to scan this data periodically and save some
statistics on it--the way ANALYZE does--and then have that turn into a
planner input.

The related secondary idea of just making assumptions about small
tables/indexes, too, may be a useful heuristic to layer on top of this.
There's a pile of ideas here that all seem reasonable both in terms of
modeling real-world behavior and as things that could be inserted into
the optimizer. As usual, I suspect that work is needs to be followed by
a giant testing exercise though.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books