"Zeugswetter Andreas ADI SD" <[EMAIL PROTECTED]> 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! ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
