On 4/29/2011 1:55 PM, Greg Smith wrote:
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.
How about if the stats were kept, but had no affect on plans, or
optimizer or anything else.
It would be a diag tool. When someone wrote the list saying "AH! It
used the wrong index!". You could say, "please post your config
settings, and the stats from 'select * from pg_stats_something'"
We (or, you really) could compare the seq_page_cost and random_page_cost
from the config to the stats collected by PG and determine they are way
off... and you should edit your config a little and restart PG.
-Andy
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
