Re: Lots of read activity on index only scan

On 2022-11-18 13:09:16 -0800, Peter Geoghegan wrote:
> On Fri, Nov 18, 2022 at 12:46 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
> > Both do a parallel index only scan. Both perform 0 heap fetches.
> > But one reads 27336 buffers (or about 22 bytes per index entry, which
> > sounds reasonable) while the other reads 9995216 buffers (or almost one
> > full buffer per row). Why? The entries should be dense in the index in
> > both cases and since it's an index only scan (and explain says there
> > were 0 heap fetches) I would not expect extra accesses. Where do these
> > buffer reads come from?
>
> The index-only scan processes an index leaf page at a time.

There should be about 27000 of them, same as for the othe index, right?

> 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?

Hmm, that would explain why all the accesses are hits. The whole table
definitely doesn't fit into work_mem.

> 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.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"