Re: Performance

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(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