Re: Report buffer access strategies in EXPLAIN

Hi,

On Tue, Oct 27, 2026 at 01:56:16PM -0500, Sami Imseih wrote:
> Hi,
>
> > While working on [1], I realized that it could be useful to report buffer
> > access strategies in EXPLAIN. This would show whether a scan uses normal
> > buffer replacement or a ring intended to limit cache pollution. Tomas also
> > mentioned
> > this in [2], with support from Andres. So, PFA a five patch series to do so.
>
> +1. I think this is a good idea. BufferAccessStrategy has historically been
> a fairly hidden part of buffer management, and loosely documented from a
> user perspective. With pg_stat_io exposing it, it seems logical to me to also
> include it in EXPLAIN (ANALYZE, BUFFERS).
>
> I took a look at the patchset.

Thanks for looking at it!

> v1-0001:
>
> Maybe:
>
> "The ring is enlarged beyond the 256KB minimum by up to io_combine_limit *"
>
> That makes it clear that 256KB is the minimum.

I don't think 256KB is always the minimum, as GetAccessStrategyWithSize() caps
the actual ring size at 1/8th of shared_buffers. For example, with shared_buffers
set to 1MB, the ring contains 128KB. I've reworded this part in v2 to distinguish
the requested size from the actual size.

> v1-0002:
>
> Looks good overall. One wording suggestion:
>
> Maybe:
>
> "buffers avoids evicting unrelated pages from shared buffers."
>
> That seems closer to the usual shared-buffer terminology.

Yeah, that wording is clearer. I used "reduces eviction" though, as the strategy
does not necessarily avoid such evictions entirely.

> Some additional documentation points:
>
> In my recent talk at PG Summit NY, someone asked whether a page that is
> already in shared buffers would be read again if it is later read through a
> ring. My answer was no, and I think that is right.

Yeah, that's right.

> But then I noticed that the pg_stat_io docs describe the bulkread/bulkwrite/
> vacuum contexts as I/O done "outside of shared buffers" [1]. I think that is
> easy to misread. Strategy rings still use shared buffers; they are not
> separate. If a page is already in shared buffers, a later bulkread access can
> hit it there rather than reading it again.

The existing glossary already describes this correctly, but the pg_stat_io
descriptions does not. Fixed in a new dedicated 0003.

> Also, src/backend/storage/buffer/README says bulk writes currently apply only
> to COPY IN and CREATE TABLE AS SELECT. That looks outdated now that
> GetBulkInsertState() is also used by CREATE MATERIALIZED VIEW, REFRESH
> MATERIALIZED VIEW, and table rewrite paths.

> Maybe we should clarify these in this patchset as well?

Indeed, done in 0001 while at it. I made the list non exhaustive and changed it
to mention COPY FROM, CREATE TABLE AS, materialized view population, and some
table rewrites.

I also took care of the other comments and ended up with this split:

0004 refactors the table scan instrumentation lookup.
0005 adds the common buffer strategy instrumentation.
0006 adds the missing per worker instrumentation for SampleScan and TidScan,
fixing the reported reproducer (nice catch, thanks!)
0007 reports strategies for all supported table scan types.
0008 reports strategies for index scans.

I removed the rescan assertion and now retain the most recently reported strategy,
as on the table AM side. Reporting mixed would be more precise if an extension
changes strategy on rescan, but I preferred to keep it simple for now since in
core AMs do not do that.

The accumulation helpers have been renamed to CombineTableScanInstrumentation()
and CombineIndexScanInstrumentation(), and index only scan coverage has been added.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com