| 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 |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Greg Burd | 2026-10-27 12:38:30 | Re: Hash index inserts serialise on the metapage tuple counter |
| Previous Message | Greg Burd | 2026-10-27 11:57:37 | Re: ZSTD TOAST compression, and an extensible compression method encoding |