Re: BTree on-disk page ordering

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> The mention of the changes to the btree scan code in the latest weekly
> news got me curious so I started looking at the 'executive summary'
> (read as: README) of the patch changes for both the scan patch and the
> btbulkdelete patch. If my understanding is correct, vacuum will only see
> a speed improvement when an index's on-disk storage order has a low
> correlation to index order. Is there any way to see what that
> correlation is on a running system? I'm wondering if anyone has checked
> to see what kind of performance impact a highly out-of-order index has
> on index scans.

There's nothing built in. If you feel like hacking something, I've
attached a truly ugly tool that I've used once or twice in the past to
debug broken indexes. It's got some smarts about detecting inconsistent
index structure and it looks like this latest version was meant to dump
out the index keys of a particular index. You could modify it to just
scan the level-zero pages and compute some statistics about their
ordering.

regards, tom lane