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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
