On Thu, Jul 24, 2025 at 7:19 AM Tomas Vondra <to...@vondra.me> wrote: > I got a bit bored yesterday, so I gave this a try and whipped up a patch > that adds two pgstattuple functins that I think could be useful for > analyzing index metrics that matter for prefetching.
This seems quite useful. I notice that you're not accounting for posting lists. That'll lead to miscounts of the number of heap blocks in many cases. I think that that's worth fixing, even given that this patch is experimental. > It's trivial to summarize this into a per-index statistic (of course, > there may be some inaccuracies when the run spans multiple ranges), but > it also seems useful to be able to look at parts of the index. FWIW in my experience, the per-leaf-page "nhtids:nhblks" tends to be fairly consistent across all leaf pages from a given index. There are no doubt some exceptions, but they're probably pretty rare. > Second, the index is walked sequentially in physical order, from block 0 > to the last block. But that's not really what the index prefetch sees. > To make it "more accurate" it'd be better to just scan the leaf pages as > if during a "full index scan". Why not just do it that way to begin with? It wouldn't be complicated to make the function follow a chain of right sibling links. I suggest an interface that takes a block number, and an nblocks int8 argument that must be >= 1. The function would start from the block number arg leaf page. If it's not a non-ignorable leaf page, throw an error. Otherwise, count the number of distinct heap blocks on the leaf page, and count the number of heap blocks on each additional leaf page to the right -- until we've counted the heap blocks from nblocks-many leaf pages (or until we reach the rightmost leaf page). I suggest that a P_IGNORE() page shouldn't have its heap blocks counted, and shouldn't count towards our nblocks tally of leaf pages whose heap blocks are to be counted. Upon encountering a P_IGNORE() page, just move to the right without doing anything. Note that the rightmost page cannot be P_IGNORE(). This scheme will always succeed, no matter the nblocks argument, provided the initial leaf page is a valid leaf page (and provided the nblocks arg is >= 1). I get that this is just a prototype that might not go anywhere, but the scheme I've described requires few changes. -- Peter Geoghegan
