James Mansion wrote:
Does the server know which IO it thinks is sequential, and which it thinks is random? Could it not time the IOs (perhaps optionally) and at least keep some sort of statistics of the actual observed times?
It makes some assumptions based on what the individual query nodes are doing. Sequential scans are obviously sequential; index lookupss random; bitmap index scans random.
The "measure the I/O and determine cache state from latency profile" has been tried, I believe it was Greg Stark who ran a good experiment of that a few years ago. Based on the difficulties of figuring out what you're actually going to with that data, I don't think the idea will ever go anywhere. There are some really nasty feedback loops possible in all these approaches for better modeling what's in cache, and this one suffers the worst from that possibility. If for example you discover that accessing index blocks is slow, you might avoid using them in favor of a measured fast sequential scan. Once you've fallen into that local minimum, you're stuck there. Since you never access the index blocks, they'll never get into RAM so that accessing them becomes fast--even though doing that once might be much more efficient, long-term, than avoiding the index.
There are also some severe query plan stability issues with this idea beyond this. The idea that your plan might vary based on execution latency, that the system load going up can make query plans alter with it, is terrifying for a production server.
-- 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
