Report buffer access strategies in EXPLAIN

From: Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Report buffer access strategies in EXPLAIN
Date: 2026-10-27 12:26:09
Message-ID: auCYYbDyrQEwIsQq@bdtpg
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

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.

=== 0001

Fix an oversight in commit 15f0cb26b530, which left the buffer README describing
the bulk-read strategy as using a fixed 256kB ring. It now starts at 256kB and
grows according to the configured I/O concurrency.

=== 0002

Describe the normal, bulkread, bulkwrite, and vacuum strategies in the glossary,
and link to these descriptions where appropriate.

I think 0001 and 0002 could be committed independently of the discussion around
the remaining patches.

=== 0003

Extract leader and worker table scan instrumentation lookup from show_scan_io_usage(),
so that it can be reused by other instrumentation.

=== 0004

Report the strategy used by sequential, sample, bitmap heap, TID, and TID range
scans when ANALYZE and BUFFERS are enabled.

This reuses the table scan instrumentation and parallel worker infrastructure
introduced by commit 681daed93169 and its follow up commits.

A validity flag allows a table AM not to expose the strategy without it being
mistaken for BAS_NORMAL. In parallel plans, the strategy is reported only for
processes that execute the scan.

=== 0005

Extend the same reporting to index, index only, and bitmap index scans.
The core index AMs report normal, while Bloom reports bulkread because it reads
the complete index using a bulk read strategy.

0004 and 0005 include documentation and TAP coverage, including parallel
leader/worker reporting.

Remark:

I initially planned to report more details, including the ring size and ring
activity counters. However, after discussing it with Andres at PGConf.EU, we
agreed to keep it simple for now and only report the strategy type.

[1]: https://postgr.es/m/atHWFmxAz1KuKzqm%40bdtpg
[2]: https://postgr.es/m/mu75uaxc2zm7m4izyjucbsfdsy3ehdnueladts2ana5fbirqbv@tnbup5uc5yqc

Regards,

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

Attachment Content-Type Size
v1-0001-Update-bulk-read-buffer-ring-description-in-the-b.patch text/x-diff 3.6 KB
v1-0002-Describe-buffer-access-strategy-types-in-the-glos.patch text/x-diff 7.5 KB
v1-0003-Refactor-table-scan-instrumentation-lookup.patch text/x-diff 5.8 KB
v1-0004-Report-buffer-access-strategy-for-table-scans-in-.patch text/x-diff 43.4 KB
v1-0005-Report-buffer-access-strategy-for-index-scans-in-.patch text/x-diff 21.0 KB

Browse pgsql-hackers by date

  From Date Subject
Previous Message Greg Burd 2026-10-27 11:57:37 Re: ZSTD TOAST compression, and an extensible compression method encoding