"Zeugswetter Andreas ADI SD" <Andreas.Zeugswetter@s-itsolutions.at> writes:
> Sorry, but I don't grok this at all. Why the heck would we care if we have 2
> parts of the table perfectly clustered, because we started in the middle ?
> Surely our stats collector should recognize such a table as perfectly
> clustered. Does it not ? We are talking about one breakage in the readahead
> logic here, this should only bring the clustered property from 100% to some
> 99.99% depending on table size vs readahead window.
Well clusteredness is used or could be used for a few different heuristics,
not all of which this would be quite as well satisfied as readahead. But for
the most common application, namely trying to figure out whether index probes
for sequential ids will be sequential i/o or random i/o you're right.
Currently the statistic we use to estimate this is the correlation of the
column value with the physical location on disk. That's not a perfect metric
for estimating how much random i/o would be needed to scan the table in index
order though.
It would be great if Postgres picked up a serious statistics geek who could
pipe up in discussions like this with "how about using the Euler-Jacobian
Centroid" or some such thing. If you have any suggestions of what metric to
use and how to calculate the info we need from it that would be great.
One suggestion from a long way back was scanning the index and counting how
many times the item pointer moves backward to an earlier block. That would
still require a full index scan though. And it doesn't help for columns which
aren't indexed though I'm not sure we need this info for columns which aren't
indexed. It's also not clear how to interpolate from that the amount of random
access a given query would perform.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!