Re: Lots of read activity on index only scan

On Fri, Nov 18, 2022 at 1:50 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
> There should be about 27000 of them, same as for the othe index, right?

There aren't that many. The point I'm making is that you can access
each VM page approximately once (and check relatively many index
tuple's TIDs all in one go), or many times. The total number of VM
pages may be constant, but the access patterns are quite different
owing to differences in how the data is clustered in each index.

> > When there is naturally a high correlation (or some kind of
> > clustering) in how we access VM pages, we'll naturally be able to do
> > more visibility checks covering more index tuples per VM page
> > accessed.
>
> So you are saying that these are accesses to the visibility map, not the
> base table?

Yes. I see "Heap Fetches: 0" for both plans, that each query the same
table and scan approximately the same number of index pages. So VM
accesses are the only explanation that makes any sense.

> > Also worth bearing in mind that it's unusual to have a perfectly
> > random and uniformly distributed clustering of index tuples,
>
> Sure. This is a highly contrived example.

FWIW I think that it could be a lot less bad, even with indexes that
you'd think would be almost as bad as the bad one from your test case.
Even things that appear to be random aren't usually nearly as random
as what you've shown.

--
Peter Geoghegan