Report buffer access strategies in EXPLAIN
Hi hackers,
While working on [1]/messages/by-id/atHWFmxAz1KuKzqm@bdtpg, 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]/messages/by-id/mu75uaxc2zm7m4izyjucbsfdsy3ehdnueladts2ana5fbirqbv@tnbup5uc5yqc, 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]: /messages/by-id/atHWFmxAz1KuKzqm@bdtpg
[2]: /messages/by-id/mu75uaxc2zm7m4izyjucbsfdsy3ehdnueladts2ana5fbirqbv@tnbup5uc5yqc
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
t254410_1v1-0001-Update-bulk-read-buffer-ring-description-in-the-b.patchtext/x-diff; charset=us-asciiDownload+20-14
v1-0002-Describe-buffer-access-strategy-types-in-the-glos.patchtext/x-diff; charset=us-asciiDownload+51-10
v1-0003-Refactor-table-scan-instrumentation-lookup.patchtext/x-diff; charset=us-asciiDownload+80-79
v1-0004-Report-buffer-access-strategy-for-table-scans-in-.patchtext/x-diff; charset=us-asciiDownload+514-150
v1-0005-Report-buffer-access-strategy-for-index-scans-in-.patchtext/x-diff; charset=us-asciiDownload+301-16
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.
v1-0001:
One small nit:
This:
+The ring is enlarged to accommodate io_combine_limit *
Maybe:
"The ring is enlarged beyond the 256KB minimum by up to io_combine_limit *"
That makes it clear that 256KB is the minimum.
v1-0002:
Looks good overall. One wording suggestion:
This:
+ of buffers reduces the displacement of unrelated cached data.
reads a bit odd to me. Maybe:
"buffers avoids evicting unrelated pages from shared buffers."
That seems closer to the usual shared-buffer terminology.
v1-0003:
This one looks mechanical and I could not find any issues with it.
v1-0004:
I found one issue with SampleScan in parallel plans.
The patch adds SampleScan to the code that prints the buffer access strategy,
but `get_table_scan_instrumentation()` still only deals with worker
instrumentation for these scan types:
```
switch (nodeTag(planstate->ps.plan))
{
case T_BitmapHeapScan:
case T_SeqScan:
case T_TidRangeScan:
...
}
```
So if a SampleScan is executed only by a worker, its strategy is not printed.
I can reproduce this with:
```
SET debug_parallel_query = on;
SET max_parallel_workers_per_gather = 2;
SET parallel_leader_participation = off;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM repro_strategy TABLESAMPLE SYSTEM (100);
```
`TidScan` has the same worker-reporting issue, but it only reports `normal`
today because `table_beginscan_tid()` does not allow a non-default strategy.
v1-0005:
This looks good to me overall, but I have a couple of small comments.
1/
One question about the assertion in `InstrRecordIndexScanStrategy()`:
```
Assert(!instrument->strategy_valid ||
instrument->strategy == strategy);
```
This assumes that the strategy cannot change on rescan, and that seems to be
true for all in-tree callers now. Not sure how this holds up for extension
index AMs.
2/
Small naming/comment nit: `AccumulateIndexScanInstrumentation()` accumulates
the counters, but the strategy field is copied and checked against the
previous value. So the function name and comment do not describe that part
correctly. It is not just accumulating.
3/
Should the tests also cover index-only scans? They go through a separate
executor path.
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.
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]https://www.postgresql.org/docs/18/monitoring-stats.html. 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.
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?
[1]: https://www.postgresql.org/docs/18/monitoring-stats.html
--
Sami Imseih
Amazon Web Services (AWS)
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
Attachments:
t254410_3v2-0001-Update-buffer-strategy-descriptions-in-the-buffer.patchtext/x-diff; charset=us-asciiDownload+24-18
v2-0002-Describe-buffer-access-strategy-types-in-the-glos.patchtext/x-diff; charset=us-asciiDownload+51-10
v2-0003-Clarify-buffer-access-strategies-in-pg_stat_io-do.patchtext/x-diff; charset=us-asciiDownload+20-15
v2-0004-Refactor-table-scan-instrumentation-lookup.patchtext/x-diff; charset=us-asciiDownload+80-79
v2-0005-Add-buffer-access-strategy-to-table-scan-instrume.patchtext/x-diff; charset=us-asciiDownload+111-40
v2-0006-Add-table-scan-instrumentation-for-sample-and-TID.patchtext/x-diff; charset=us-asciiDownload+324-12
v2-0007-Report-buffer-access-strategy-for-table-scans-in-.patchtext/x-diff; charset=us-asciiDownload+454-110
v2-0008-Report-buffer-access-strategy-for-index-scans-in-.patchtext/x-diff; charset=us-asciiDownload+326-16
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.
I think this is an important point for what the "Buffer Access
Strategy:" field means.
IMO we should make the rule as: normal does not overwrite an already-recorded
non-normal strategy.
There is a core case here too.
For example, I added this instrumentation:
```
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1327,6 +1327,10 @@ heap_beginscan(Relation relation, Snapshot snapshot,
{
scan->rs_base.rs_instrument->strategy =
GetAccessStrategyType(scan->rs_strategy);
scan->rs_base.rs_instrument->strategy_valid = true;
+ elog(NOTICE, "heap_beginscan strategy: scan=%p
rel=%s strategy=%d",
+ (void *) scan,
+ RelationGetRelationName(scan->rs_base.rs_rd),
+ scan->rs_base.rs_instrument->strategy);
}
/* read stream instrumentation */
@@ -1402,6 +1406,10 @@ heap_rescan(TableScanDesc sscan, ScanKey key,
bool set_params,
{
scan->rs_base.rs_instrument->strategy =
GetAccessStrategyType(scan->rs_strategy);
scan->rs_base.rs_instrument->strategy_valid = true;
+ elog(NOTICE, "heap_rescan strategy: scan=%p rel=%s strategy=%d",
+ (void *) scan,
+ RelationGetRelationName(scan->rs_base.rs_rd),
+ scan->rs_base.rs_instrument->strategy);
}
}
```
and ran this case in which a parameterized TABLESAMPLE SYSTEM scan can move
between normal and bulkread across rescans depending on the outer parameter
value.
```
postgres=# SET client_min_messages = notice;
DROP TABLE IF EXISTS sample_strategy_repro;
CREATE TABLE sample_strategy_repro AS
SELECT g AS i
FROM generate_series(1, 2200000) AS g;
ANALYZE sample_strategy_repro;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM (
SELECT p
FROM (VALUES (1, 100::real), (2, 0.5::real)) v(ord, p)
ORDER BY ord
) o
JOIN LATERAL (
SELECT *
FROM sample_strategy_repro TABLESAMPLE SYSTEM (o.p) REPEATABLE (0)
) s ON true;
SET
NOTICE: table "sample_strategy_repro" does not exist, skipping
DROP TABLE
SELECT 2200000
ANALYZE
NOTICE: heap_beginscan strategy: scan=0x1b2fa8c8
rel=sample_strategy_repro strategy=1
NOTICE: heap_rescan strategy: scan=0x1b2fa8c8
rel=sample_strategy_repro strategy=0
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=17732.06..17732.07 rows=1 width=8) (actual
time=1183.289..1183.292 rows=1.00 loops=1)
Buffers: shared hit=12081 read=7488 dirtied=7489 written=57
-> Nested Loop (cost=0.04..16632.06 rows=440000 width=0) (actual
time=0.353..963.297 rows=2210396.00 loops=1)
Buffers: shared hit=12081 read=7488 dirtied=7489 written=57
-> Sort (cost=0.04..0.04 rows=2 width=8) (actual
time=0.012..0.016 rows=2.00 loops=1)
Sort Key: "*VALUES*".column1
Sort Method: quicksort Memory: 25kB
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2
width=8) (actual time=0.003..0.005 rows=2.00 loops=1)
-> Sample Scan on sample_strategy_repro (cost=0.00..6116.00
rows=220000 width=0) (actual time=0.204..376.754 rows=1105198.00
loops=2)
Sampling: system ("*VALUES*".column2) REPEATABLE
('0'::double precision)
Buffer Access Strategy: normal
Buffers: shared hit=12081 read=7488 dirtied=7489 written=57
Planning:
Buffers: shared hit=87 read=7
Planning Time: 0.639 ms
Execution Time: 1183.341 ms
(16 rows)
```
I still do not think we need to show all the strategies used here, at least not
for the first version of this patch. What the user will care about most is
which ring strategy was used, so if anything other than normal was used, that
should take precedence. This should be documented.
I also have a few additional comments by patch:
v2-0002:
The glossary still reads as if all Buffer Access Strategies are ring-based, but
normal is listed below and explicitly does not use a strategy ring. I think this
wording would avoid that:
```
- A Buffer Access Strategy sets up references to a limited number of
- <glossterm linkend="glossary-shared-memory">shared buffers</glossterm> and
- reuses them circularly. When the operation requires a new page, a victim
- buffer is chosen from the buffers in the strategy ring, which may require
+ Except for <literal>normal</literal>, Buffer Access Strategies set up
+ references to a limited number of
+ <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>
+ and reuse them circularly. When the operation requires a new page, a
+ victim buffer is chosen from the buffers in the strategy ring, which may
+ require
```
v2-0003:
In the pg_stat_io docs, I think the bulkwrite example should say COPY FROM
rather than COPY:
```
- strategy</glossterm>, such as <command>COPY</command>.
+ strategy</glossterm>, such as <command>COPY FROM</command>.
```
v2-0007:
I think the EXPLAIN docs should clarify that this field does not report
bulkwrite. For example, CTAS can use BAS_BULKWRITE for the target table, but
EXPLAIN only reports the source scan strategy here. I could not find an
EXPLAINable SQL command that would show bulkwrite through this field.
Maybe something like:
```
nodes report the <glossterm
linkend="glossary-buffer-access-strategy">buffer access
- strategy</glossterm> used by the table scan.
+ strategy</glossterm> used by the table scan. This field reports
+ strategies for scan nodes; it does not show write-side strategies such as
+ <literal>bulkwrite</literal>, even when a command uses them internally.
```
v2-0008:
bloom records BAS_BULKREAD in blrescan(), before it knows whether blgetbitmap()
will actually scan the index. With a generic prepared plan and a NULL parameter,
blgetbitmap() returns from the SK_ISNULL fast path, so EXPLAIN can show
"Buffer Access Strategy: bulkread" together with "Index Searches: 0".
For example:
```
CREATE EXTENSION IF NOT EXISTS bloom;
DROP TABLE IF EXISTS bloom_strategy_repro;
CREATE TABLE bloom_strategy_repro (i int);
INSERT INTO bloom_strategy_repro
SELECT generate_series(1, 1000);
CREATE INDEX bloom_strategy_repro_idx
ON bloom_strategy_repro USING bloom (i);
ANALYZE bloom_strategy_repro;
SET enable_seqscan = off;
SET plan_cache_mode = force_generic_plan;
PREPARE bloom_q(int) AS
SELECT *
FROM bloom_strategy_repro
WHERE i = $1;
EXPLAIN (ANALYZE, BUFFERS)
EXECUTE bloom_q(NULL);
```
Before the fix, the Bitmap Index Scan reports both:
```
Buffer Access Strategy: bulkread
Index Searches: 0
```
I think InstrRecordIndexScanStrategy(..., BAS_BULKREAD) should move to
blgetbitmap(), just before GetAccessStrategy(BAS_BULKREAD), after the NULL-key
early return:
```
@@ blrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys,
- InstrRecordIndexScanStrategy(scan->instrument, BAS_BULKREAD);
-
if (so->sign)
pfree(so->sign);
@@ blgetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
/*
* We're going to read the whole index. This is why we use appropriate
* buffer access strategy.
*/
+ InstrRecordIndexScanStrategy(scan->instrument, BAS_BULKREAD);
bas = GetAccessStrategy(BAS_BULKREAD);
```
Maybe as a follow-up, but showing the ring size too seems useful also.
--
Sami
Hi,
On Wed, Oct 28, 2026 at 04:21:49PM -0500, Sami Imseih wrote:
There is a core case here too.
Oh right, thanks for looking at it!
I still do not think we need to show all the strategies used here, at least not
for the first version of this patch. What the user will care about most is
which ring strategy was used, so if anything other than normal was used, that
should take precedence. This should be documented.
I had more thoughts about it and I think that both strategies are important, as
both affect buffer replacement and therefore may impact the workload.
What about retaining all strategies observed across rescans? The instrumentation
could use a bitmask and EXPLAIN would report them as a list, for example:
Buffer Access Strategies: normal, bulkread
That would preserve all relevant information without giving one strategy precedence.
Would that make sense?
I also have a few additional comments by patch:
v2-0002:
The glossary still reads as if all Buffer Access Strategies are ring-based, but
normal is listed below and explicitly does not use a strategy ring. I think this
wording would avoid that:``` - A Buffer Access Strategy sets up references to a limited number of - <glossterm linkend="glossary-shared-memory">shared buffers</glossterm> and - reuses them circularly. When the operation requires a new page, a victim - buffer is chosen from the buffers in the strategy ring, which may require + Except for <literal>normal</literal>, Buffer Access Strategies set up + references to a limited number of + <glossterm linkend="glossary-shared-memory">shared buffers</glossterm> + and reuse them circularly. When the operation requires a new page, a + victim buffer is chosen from the buffers in the strategy ring, which may + require ```
Agree, will do.
v2-0003:
In the pg_stat_io docs, I think the bulkwrite example should say COPY FROM
rather than COPY:
Agreed.
v2-0007:
I think the EXPLAIN docs should clarify that this field does not report
bulkwrite.
I think that was implied by "plan nodes report the strategies used by the
table or index scan" but better to remove ambiguity as you suggest.
v2-0008:
I think InstrRecordIndexScanStrategy(..., BAS_BULKREAD) should move to
blgetbitmap(), just before GetAccessStrategy(BAS_BULKREAD), after the NULL-key
early return
Good catch, will do that.
Maybe as a follow-up, but showing the ring size too seems useful also.
Yeah, I agree that reporting the ring size could be useful. As mentioned in the
first email, I initially planned to report it, together with ring activity
counters, but after discussing it with Andres at PGConf.EU we agreed to keep the
first version simple. So I’d prefer to leave that for a follow-up.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
I still do not think we need to show all the strategies used here, at least not
for the first version of this patch. What the user will care about most is
which ring strategy was used, so if anything other than normal was used, that
should take precedence. This should be documented.I had more thoughts about it and I think that both strategies are important, as
both affect buffer replacement and therefore may impact the workload.What about retaining all strategies observed across rescans? The instrumentation
could use a bitmask and EXPLAIN would report them as a list, for example:Buffer Access Strategies: normal, bulkread
This makes sense, but now it gets me thinking...
If EXPLAIN reports multiple strategies for a node, I wonder whether we will
also need to break down the existing shared buffer counters by access strategy.
Something like:
```
Buffers: shared hit=95 read=8482 written=12
Strategy Buffers: normal hit=1, bulkread hit=94 read=8482 written=12
```
I think the breakdown should follow pg_stat_io's IOContext attribution. That
means shared hit/read, and probably written too, because those paths already
have an IOContext. I would leave dirtied out, since that does not seem to be
attributed to an IOContext today, based on my quick look at the code.
These per-strategy counters could be tracked in pgBufferUsage and bubbled up
to EXPLAIN the same way the existing buffer counters are.
What do you think?
--
Sami Imseih
Amazon Web Services (AWS)
Hi,
On Thu, Oct 29, 2026 at 12:30:08PM -0500, Sami Imseih wrote:
What about retaining all strategies observed across rescans? The instrumentation
could use a bitmask and EXPLAIN would report them as a list, for example:Buffer Access Strategies: normal, bulkread
This makes sense,
Thanks for confirming.
but now it gets me thinking...
If EXPLAIN reports multiple strategies for a node, I wonder whether we will
also need to break down the existing shared buffer counters by access strategy.Something like:
```
Buffers: shared hit=95 read=8482 written=12
Strategy Buffers: normal hit=1, bulkread hit=94 read=8482 written=12
```I think the breakdown should follow pg_stat_io's IOContext attribution. That
means shared hit/read, and probably written too, because those paths already
have an IOContext. I would leave dirtied out, since that does not seem to be
attributed to an IOContext today, based on my quick look at the code.These per-strategy counters could be tracked in pgBufferUsage and bubbled up
to EXPLAIN the same way the existing buffer counters are.What do you think?
I agree that such a breakdown could be useful. It answers a different question
though: the current patch reports which strategies were selected, while those
counters would show how much buffer activity occurred under each strategy.
For this first version, I’d prefer the patch series to focus on getting strategy
reporting right while keeping the additional instrumentation relatively light.
Also, pg_stat_get_backend_io(pg_backend_pid()) already provides backend wide
counters broken down by I/O context. It lacks per plan node attribution but it
may already cover some diagnostic use cases.
So I’d prefer to consider that as a follow up. Does that make sense?
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
What about retaining all strategies observed across rescans? The instrumentation
could use a bitmask and EXPLAIN would report them as a list, for example:Buffer Access Strategies: normal, bulkread
This makes sense,
Thanks for confirming.
but now it gets me thinking...
If EXPLAIN reports multiple strategies for a node, I wonder whether we will
also need to break down the existing shared buffer counters by access strategy.Something like:
```
Buffers: shared hit=95 read=8482 written=12
Strategy Buffers: normal hit=1, bulkread hit=94 read=8482 written=12
```I think the breakdown should follow pg_stat_io's IOContext attribution. That
means shared hit/read, and probably written too, because those paths already
have an IOContext. I would leave dirtied out, since that does not seem to be
attributed to an IOContext today, based on my quick look at the code.These per-strategy counters could be tracked in pgBufferUsage and bubbled up
to EXPLAIN the same way the existing buffer counters are.What do you think?
I agree that such a breakdown could be useful. It answers a different question
though: the current patch reports which strategies were selected, while those
counters would show how much buffer activity occurred under each strategy.For this first version, I’d prefer the patch series to focus on getting strategy
reporting right while keeping the additional instrumentation relatively light.
Yes, that's the correct approach.
Also, pg_stat_get_backend_io(pg_backend_pid()) already provides backend wide
counters broken down by I/O context. It lacks per plan node attribution but it
may already cover some diagnostic use cases.
Right, but that's a different tool. per-node attribution of buffers per strategy
is the natural next step of this feature, IMO.
So I’d prefer to consider that as a follow up. Does that make sense?
Yes, I am ok with this.
--
Sami
Hi,
On Mon, Nov 02, 2026 at 06:11:29AM -0600, Sami Imseih wrote:
For this first version, I’d prefer the patch series to focus on getting strategy
reporting right while keeping the additional instrumentation relatively light.Yes, that's the correct approach.
So I’d prefer to consider that as a follow up. Does that make sense?
Yes, I am ok with this.
Thanks for confirming!
PFA v3 with the changes discussed up-thread. In addition, I adjusted SP-GiST to
record the strategy only when it is about to walk the index. I also clarified
that table scan strategies are reported only when provided by the table AM and
added comments to the new TAP tests.
To sum up, we have this 8 patch series:
0001 updates the buffer README to describe the current bulk-read ring sizing and
bulk-write users.
0002 documents the available buffer access strategy types.
0003 clarifies that strategy rings reference shared buffers in the pg_stat_io
documentation.
The above could be pushed independently of the followings.
0004 extracts the lookup of leader and per-worker table scan instrumentation.
0005 adds optional buffer access strategy instrumentation for table scans and
preserves every strategy observed across rescans.
0006 adds per-worker table scan instrumentation for sample and TID scans.
0007 reports the strategies used by table scans with EXPLAIN (ANALYZE, BUFFERS),
including parallel workers.
0008 reports the strategies used by index, index-only, and bitmap index scans.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
t254410_9v3-0001-Update-buffer-strategy-descriptions-in-the-buffer.patchtext/x-diff; charset=us-asciiDownload+24-18
v3-0002-Describe-buffer-access-strategy-types-in-the-glos.patchtext/x-diff; charset=us-asciiDownload+51-10
v3-0003-Clarify-buffer-access-strategies-in-pg_stat_io-do.patchtext/x-diff; charset=us-asciiDownload+20-15
v3-0004-Refactor-table-scan-instrumentation-lookup.patchtext/x-diff; charset=us-asciiDownload+80-79
v3-0005-Add-buffer-access-strategies-to-table-scan-instru.patchtext/x-diff; charset=us-asciiDownload+124-34
v3-0006-Add-table-scan-instrumentation-for-sample-and-TID.patchtext/x-diff; charset=us-asciiDownload+324-12
v3-0007-Report-buffer-access-strategies-for-table-scans-i.patchtext/x-diff; charset=us-asciiDownload+503-110
v3-0008-Report-buffer-access-strategies-for-index-scans-i.patchtext/x-diff; charset=us-asciiDownload+375-17
On Tue, Nov 03, 2026 at 10:15:48AM +0000, Bertrand Drouvot wrote:
0001 updates the buffer README to describe the current bulk-read ring sizing and
bulk-write users.
I have looked at v1~3, as these are actual doc-"bug" fixes.
Perhaps not strictly mandatory for a README, but I'm wondering if this
should be backpatched down to v18.. If that applies without
conflicts.
-Bulk writes work similarly to VACUUM. Currently this applies only to
-COPY IN and CREATE TABLE AS SELECT. (Might it be interesting to make
-seqscan UPDATE and DELETE use the bulkwrite strategy?) For bulk writes
-we use a ring size of 16MB (but not more than 1/8th of shared_buffers).
+Bulk writes work similarly to VACUUM. They are used for operations such as
+COPY FROM, CREATE TABLE AS, materialized view population, and some table
+rewrites. For bulk writes we use a ring size of 16MB (but not more than
+1/8th of shared_buffers).
Perhaps just drop the list of operations or be more vague? This
misses for example CREATE DATABASE.
+For bulk reads, the requested ring size is at least 256KB. The 256KB target is
+small enough to fit in L2 cache, which makes transferring pages from OS cache
+to shared buffer cache efficient.
The ring can be enlarged, meaning that the optimization applies only
for a minimum size?
0002 documents the available buffer access strategy types.
Specifies the size of the
+ <literal>vacuum</literal>
<glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
[...]
maintenance tasks (such as index vacuuming) are bypassed, and any
+ <literal>vacuum</literal>
<glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
in use will be disabled resulting in <command>VACUUM</command> being
[...]
Specifies the
+ <literal>vacuum</literal>
<glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
ring buffer size for <command>ANALYZE</command>. This size is used to
[...]
ring buffer size for <command>VACUUM</command>. This size is used to
calculate the number of shared buffers which will be reused as part of
- this strategy. <literal>0</literal> disables use of a
+ this strategy. <literal>0</literal> disables use of a <literal>vacuum</literal>
<literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>
These changes seem a bit weird to me. Okay, the BAS is named
"vacuum", and the contexts refer to VACUUM, ANALYZE or vacuumdb, but
that feels duplicated wording to me. I'm wondering if it's not
simpler to just drop this level of details.
- Buffer Access Strategies are used for various operations such as
- sequential scans of large tables, <command>VACUUM</command>,
- <command>COPY</command>, <command>CREATE TABLE AS SELECT</command>,
- <command>ALTER TABLE</command>, <command>CREATE DATABASE</command>,
- <command>CREATE INDEX</command>, and <command>CLUSTER</command>.
- </para>
We are loosing some information here. Shouldn't the various
strategies still list at least these commands?
On the glossary page, we have this sentence:
A Buffer Access Strategy sets up references to a limited number of
<glossterm linkend="glossary-shared-memory">shared buffers</glossterm> and
reuses them circularly.
GetAccessStrategy() returns NULL for BAS_NORMAL, so that feels
slightly inconsistent, but I get that you've done so to be more in
line with pg_stat_io that lists a "normal". Perhaps just say that no
strategy is returned, switching to a normal clock sweep, would be more
precise?
+ little reuse, such as large sequential scans. Reusing a limited set of
+ buffers reduces eviction of unrelated pages from shared buffers.
That's in the description of bulkread, reads like a duplicate of the
paragraph above.
0003 clarifies that strategy rings reference shared buffers in the pg_stat_io
documentation.
Perhaps that should be merged with 0002. That's kind of the same set
of incorrect descriptions to me.
+ <literal>vacuum</literal>: I/O operations performed using the
+ <literal>vacuum</literal>
+ <glossterm linkend="glossary-buffer-access-strategy">buffer access
+ strategy</glossterm> while vacuuming and analyzing permanent
+ relations. Temporary table vacuums use the same local buffer pool as
+ other temporary table I/O operations and are tracked in
+ <varname>context</varname> <literal>normal</literal>.
BAS_VACUUM can also be found in the checksum code. Just saying.
0004 extracts the lookup of leader and per-worker table scan instrumentation.
0005 adds optional buffer access strategy instrumentation for table scans and
preserves every strategy observed across rescans.
0006 adds per-worker table scan instrumentation for sample and TID scans.
0007 reports the strategies used by table scans with EXPLAIN (ANALYZE, BUFFERS),
including parallel workers.
0008 reports the strategies used by index, index-only, and bitmap index scans.
Hmm. These touch the same area of the code as some of the stuff Lukas
Fittl is working on..
--
Michael
Hi,
On Thu, Nov 05, 2026 at 04:48:04PM +0900, Michael Paquier wrote:
On Tue, Nov 03, 2026 at 10:15:48AM +0000, Bertrand Drouvot wrote:
0001 updates the buffer README to describe the current bulk-read ring sizing and
bulk-write users.I have looked at v1~3, as these are actual doc-"bug" fixes.
Thanks for looking at it!
Perhaps not strictly mandatory for a README, but I'm wondering if this
should be backpatched down to v18.. If that applies without
conflicts.
Yeah that does not hurt and that apply without conflicts (just checked).
Perhaps just drop the list of operations or be more vague?
Yeah, good point. I've dropped the list in v4, which also addresses Sami's
previous comment.
+For bulk reads, the requested ring size is at least 256KB. The 256KB target is +small enough to fit in L2 cache, which makes transferring pages from OS cache +to shared buffer cache efficient.The ring can be enlarged, meaning that the optimization applies only
for a minimum size?
Right, that's why the text refers specifically to the 256KB target. Do you
mean that the L2 cache rationale should be dropped now that it does not
necessarily apply to the resulting ring size?
0002 documents the available buffer access strategy types.
Specifies the size of the + <literal>vacuum</literal> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm> [...] maintenance tasks (such as index vacuuming) are bypassed, and any + <literal>vacuum</literal> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm> in use will be disabled resulting in <command>VACUUM</command> being [...] Specifies the + <literal>vacuum</literal> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm> ring buffer size for <command>ANALYZE</command>. This size is used to [...] ring buffer size for <command>VACUUM</command>. This size is used to calculate the number of shared buffers which will be reused as part of - this strategy. <literal>0</literal> disables use of a + this strategy. <literal>0</literal> disables use of a <literal>vacuum</literal> <literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>These changes seem a bit weird to me. Okay, the BAS is named
"vacuum", and the contexts refer to VACUUM, ANALYZE or vacuumdb, but
that feels duplicated wording to me. I'm wondering if it's not
simpler to just drop this level of details.
I don't have a strong opinion about it so I removed them.
- Buffer Access Strategies are used for various operations such as
- sequential scans of large tables, <command>VACUUM</command>,
- <command>COPY</command>, <command>CREATE TABLE AS SELECT</command>,
- <command>ALTER TABLE</command>, <command>CREATE DATABASE</command>,
- <command>CREATE INDEX</command>, and <command>CLUSTER</command>.
- </para>We are loosing some information here. Shouldn't the various
strategies still list at least these commands?
That makes sense. I've listed the commands under each applicable strategy. Note
that some appear in both lists: for example, CREATE DATABASE uses bulkread for
the source and bulkwrite for the destination. I did not go as far as explaining
why in the glossary.
On the glossary page, we have this sentence:
A Buffer Access Strategy sets up references to a limited number of
<glossterm linkend="glossary-shared-memory">shared buffers</glossterm> and
reuses them circularly.GetAccessStrategy() returns NULL for BAS_NORMAL, so that feels
slightly inconsistent, but I get that you've done so to be more in
line with pg_stat_io that lists a "normal". Perhaps just say that no
strategy is returned, switching to a normal clock sweep, would be more
precise?
Yeah. I've clarified that normal uses the normal clock sweep buffer replacement
policy without a strategy ring and adjusted the preceding text accordingly.
+ little reuse, such as large sequential scans. Reusing a limited set of + buffers reduces eviction of unrelated pages from shared buffers.That's in the description of bulkread, reads like a duplicate of the
paragraph above.
Yeah, I see what you mean. Removed.
0003 clarifies that strategy rings reference shared buffers in the pg_stat_io
documentation.Perhaps that should be merged with 0002. That's kind of the same set
of incorrect descriptions to me.
I'm not sure as the pg_stat_io wording is a pre existing issue that can be
backpatched, while I think that 0002 adds documentation on master.
+ <literal>vacuum</literal>: I/O operations performed using the + <literal>vacuum</literal> + <glossterm linkend="glossary-buffer-access-strategy">buffer access + strategy</glossterm> while vacuuming and analyzing permanent + relations. Temporary table vacuums use the same local buffer pool as + other temporary table I/O operations and are tracked in + <varname>context</varname> <literal>normal</literal>.BAS_VACUUM can also be found in the checksum code. Just saying.
Good point. That wording predates f19c0eccae9 (which added BAS_VACUUM to the
checksum code), so it became stale. Fixed. This part should be kept for a v19
backpatch, but omitted for v16 through v18 (if we decide to backpatch it).
0004 extracts the lookup of leader and per-worker table scan instrumentation.
0005 adds optional buffer access strategy instrumentation for table scans and
preserves every strategy observed across rescans.
0006 adds per-worker table scan instrumentation for sample and TID scans.
0007 reports the strategies used by table scans with EXPLAIN (ANALYZE, BUFFERS),
including parallel workers.
0008 reports the strategies used by index, index-only, and bitmap index scans.Hmm. These touch the same area of the code as some of the stuff Lukas
Fittl is working on..
Yeah, this may require some rebasing. That said, I think their goals are largely
orthogonal.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
t254410_11v4-0001-Update-buffer-strategy-descriptions-in-the-buffer.patchtext/x-diff; charset=us-asciiDownload+22-18
v4-0002-Describe-buffer-access-strategy-types-in-the-glos.patchtext/x-diff; charset=us-asciiDownload+54-12
v4-0003-Clarify-buffer-access-strategies-in-pg_stat_io-do.patchtext/x-diff; charset=us-asciiDownload+20-14
v4-0004-Refactor-table-scan-instrumentation-lookup.patchtext/x-diff; charset=us-asciiDownload+80-79
v4-0005-Add-buffer-access-strategies-to-table-scan-instru.patchtext/x-diff; charset=us-asciiDownload+124-34
v4-0006-Add-table-scan-instrumentation-for-sample-and-TID.patchtext/x-diff; charset=us-asciiDownload+324-12
v4-0007-Report-buffer-access-strategies-for-table-scans-i.patchtext/x-diff; charset=us-asciiDownload+503-110
v4-0008-Report-buffer-access-strategies-for-index-scans-i.patchtext/x-diff; charset=us-asciiDownload+375-17
On Thu, Nov 5, 2026 at 4:18 AM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
0004 extracts the lookup of leader and per-worker table scan instrumentation.
0005 adds optional buffer access strategy instrumentation for table scans and
preserves every strategy observed across rescans.
0006 adds per-worker table scan instrumentation for sample and TID scans.
0007 reports the strategies used by table scans with EXPLAIN (ANALYZE, BUFFERS),
including parallel workers.
0008 reports the strategies used by index, index-only, and bitmap index scans.Hmm. These touch the same area of the code as some of the stuff Lukas
Fittl is working on..Yeah, this may require some rebasing. That said, I think their goals are largely
orthogonal.
Yep, from a quick glance I don't think this will require a lot of
coordination, just some rebasing. My work is mainly focused on
instrument.c and the executor, vs this here happens mainly in
explain.c and the node-type specific instrumentation. I do also have a
patch set that I haven't posted yet that reworks how we do parallel
worker instrumentation reporting more broadly, but I don't see any
immediate problems that would arise.
I'm aiming to do a proper review pass on this patchset as well
today/tomorrow, but wanted to comment on that quickly :)
Thanks,
Lukas
--
Lukas Fittl
Hi,
FYI, I'm also adding Tomas, Melanie and Andres as authors/reviewers of
the read stream instrumentation / IO option on this thread, since this
feels related in terms of what we surface and for whom and I'd be
curious if they have thoughts.
Comments on v4:
In 0002:
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index d28a3729188..223f90076db 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -315,13 +315,14 @@ <glossdef> <para> Some operations will access a large number of - <glossterm linkend="glossary-data-page">pages</glossterm>. A - <firstterm>Buffer Access Strategy</firstterm> helps to prevent these - operations from evicting too many pages from + <glossterm linkend="glossary-data-page">pages</glossterm>. Using a + strategy ring helps to prevent these operations from evicting too many + pages from <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>. </para> <para>
and then in 0003:
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 445fbc9232c..4da72c419c2 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml ... @@ -3340,8 +3347,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage another block. In <varname>context</varname>s <literal>bulkwrite</literal>, <literal>bulkread</literal>, and <literal>vacuum</literal>, this counts the number of times a block was - evicted from shared buffers in order to add the shared buffer to a - separate, size-limited ring buffer for use in a bulk I/O operation. + evicted from a shared buffer in order to add that buffer to a strategy + ring for use in a bulk I/O operation. </para> </entry> </row>
I personally find the terminology "strategy ring" a bit confusing. The
existing term of "size-limited ring buffer" on the pg_stat_io
documentation is more descriptive / easier to understand for me.
Is there a reason we feel that "strategy ring" is the better choice?
If we wanted to make it more clear that its a ring buffer used as part
of a buffer access strategy maybe we can do "strategy ring buffer"?
(or "strategic ring buffer"?)
---
The refactoring in 0004 seems reasonable.
In 0005:
diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index ea3f2a6be99..9ad8dde300f 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -71,8 +71,10 @@ typedef enum ScanOptions /* set if the query doesn't modify the relation */ SO_HINT_REL_READ_ONLY = 1 << 10,- /* collect scan instrumentation */ + /* collect read stream instrumentation */ SO_SCAN_INSTRUMENT = 1 << 11, + /* collect buffer access strategy instrumentation */ + SO_SCAN_INSTRUMENT_STRATEGY = 1 << 12, } ScanOptions;
Maybe the revised SO_SCAN_INSTRUMENT comment should read "collect I/O
instrumentation"? One could imagine an alternate table AM that fills
the I/O statistics some other way, or we might add other fields to it
that are not about read streams. This is in line with Tomas noting in
681daed9316 that "The collected stats are designed for ReadStream, but
are meant to be reasonably generic in case a TAM manages I/Os in
different ways.".
diff --git a/src/include/executor/instrument_node.h b/src/include/executor/instrument_node.h index 0632a72d68c..90216ee07c5 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -18,6 +18,8 @@ #ifndef INSTRUMENT_NODE_H #define INSTRUMENT_NODE_H ... @@ -78,8 +80,35 @@ typedef struct IOStats ... +/* + * Record a buffer access strategy selected by a table AM. + * + * Preserve every strategy observed across rescans. + */ +static inline void +InstrRecordTableScanStrategy(TableScanInstrumentation *instrument, + BufferAccessStrategyType strategy) +{ + if (instrument) + instrument->strategy_mask |= BufferAccessStrategyBit(strategy); +} +
I feel the "Instr" prefix is not appropriate here, since this is not
about one of the core instrumentation types defined in instrument.h,
but rather the additional table scan instrumentation, and it'd be the
first method in this file that carries that prefix. What if we just
called this "RecordTableScanStrategy", "RememberTableScanStrategy" or
"SetTableScanStrategy"?
I also wonder a bit if this helper is really needed - it feels like
you might as well just inline that code at the two call sites, and
different from the adjacently used read_stream_enable_stats I don't
really see us adding more code to this helper in the future.
---
0006 seems fine, but would be nice if we refactor some of that
repetition in the future - I don't have anything staged for that issue
specifically, FWIW, but it reminds me of the repeated code reduction
for parallel index builds at [0]/messages/by-id/CAP53Pkw3WcVbg61G29AmN=6XkuxGryoWKnhTyC1JmkT08i8qVA@mail.gmail.com.
In 0007:
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index d0ce72d83ff..735150c859e 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -4140,6 +4206,68 @@ get_table_scan_instrumentation(ScanState *planstate, int worker) return NULL; }+/* + * Show the buffer access strategy for a scan node. + */ +static void +show_scan_buffer_access_strategies(ScanState *planstate, ExplainState *es) +{ + WorkerNodeInstrumentation *worker_instrument = planstate->ps.worker_instrument; + TableScanInstrumentation *instrument; + double leader_nloops = planstate->ps.instrument ? + planstate->ps.instrument->nloops : 0; + + if (!es->buffers) + return; +
I wonder a bit if we should tie this to es->verbose (i.e. VERBOSE) as
well and not show it by default. Is this really something that every
user of EXPLAIN ANALYZE always needs to see? (since BUFFERS is now on
by default)
For example, we've also made the decision in the 19 cycle to make the
new IO option not be shown by default, and I think that one probably
has a bigger impact on performance optimization for end users than the
buffer access strategy.
One doesn't really have much control over the buffer access strategy,
so to me this is more about showing more information on how the system
works internally, which fits VERBOSE well. Reading the original
motivation on why we're adding this, it seems like its more geared
towards Postgres developers, not end users.
Alternatively, I'd argue at a minimum we should hide the buffer access
strategy when its "normal". That would still make it verbose for
queries with large sequential scans, but avoids the noise with 0008
applied.
Here is an excerpt from a practical "EXPLAIN (ANALYZE, COSTS OFF)" on
an application query I have running locally, that shows how we're
basically adding a bunch of "Buffer Access Strategies: normal" noise
that doesn't add value:
Nested Loop Semi Join (actual time=3.235..3.237 rows=0.00 loops=1)
Join Filter: (server_groups.id = organization_permissions.server_group_id)
Buffers: shared hit=329
-> Seq Scan on server_groups (actual time=0.135..0.151 rows=45.00 loops=1)
Filter: (deleted_at IS NULL)
Rows Removed by Filter: 9
Buffer Access Strategies: normal
Buffers: shared hit=1
-> Nested Loop (actual time=0.061..0.061 rows=0.00 loops=45)
Buffers: shared hit=328
-> Nested Loop (actual time=0.061..0.061 rows=0.00 loops=45)
Buffers: shared hit=328
-> Hash Join (actual time=0.053..0.055 rows=3.00 loops=45)
Hash Cond:
(organization_role_assignments.organization_role_id =
organization_permissions.organization_role_id)
Buffers: shared hit=192
-> Seq Scan on organization_role_assignments
(actual time=0.001..0.024 rows=346.00 loops=45)
Buffer Access Strategies: normal
Buffers: shared hit=180
-> Hash (actual time=0.181..0.182 rows=7.00 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=12
-> Seq Scan on organization_permissions
(actual time=0.063..0.104 rows=7.00 loops=1)
Filter: (view AND (server_group_id IS
NOT NULL))
Rows Removed by Filter: 658
Buffer Access Strategies: normal
Buffers: shared hit=12
-> Index Scan using organization_memberships_pkey on
organization_memberships (actual time=0.002..0.002 rows=0.00
loops=135)
Index Cond: (organization_membership_id =
organization_role_assignments.organization_membership_id)
Filter: ((deleted_at IS NULL) AND accepted AND
(user_id = 1))
Rows Removed by Filter: 1
Buffer Access Strategies: normal
Index Searches: 135
Buffers: shared hit=136
-> Index Only Scan using organization_roles_pkey on
organization_roles (never executed)
Index Cond: (organization_role_id =
organization_role_assignments.organization_role_id)
Heap Fetches: 0
Index Searches: 0
I'm also a bit skeptical more broadly that 0008 really adds value. How
many people use index AMs that would trigger a bulk read? (it seems
like a bunch of extra code that's unlikely to be needed in practice)
Thanks,
Lukas
[0]: /messages/by-id/CAP53Pkw3WcVbg61G29AmN=6XkuxGryoWKnhTyC1JmkT08i8qVA@mail.gmail.com
--
Lukas Fittl
On Thu, Nov 05, 2026 at 12:17:42PM +0000, Bertrand Drouvot wrote:
Right, that's why the text refers specifically to the 256KB target. Do you
mean that the L2 cache rationale should be dropped now that it does not
necessarily apply to the resulting ring size?
v4 still has the same text:
+For bulk reads, the requested ring size is at least 256KB. The 256KB target is
+small enough to fit in L2 cache, which makes transferring pages from OS cache
+to shared buffer cache efficient.
L2 caches can be larger than 256kB, so having a larger size could
still be effective. The current wording sounds slightly confusing to
me here due to that. But perhaps your wording is fine enough. I
suspect that Andres and other folks involved in this area of the code
would have comments about these parts. Better to wait for inputs from
them.
That makes sense. I've listed the commands under each applicable strategy. Note
that some appear in both lists: for example, CREATE DATABASE uses bulkread for
the source and bulkwrite for the destination. I did not go as far as explaining
why in the glossary.
The checksumming part is missing for checksums where BAS_VACUUM is in
use.
v4-0002 seems mostly OK here, with your previous adjustments included.
+ <literal>vacuum</literal>: I/O operations performed using the
+ <literal>vacuum</literal>
+ <glossterm linkend="glossary-buffer-access-strategy">buffer access
+ strategy</glossterm>
In v4-0003, too many vacuum references perhaps? This is the same as
my previous comment when I have looked at the v3 series. I'm
wondering if you should make it simpler, by the addition of linkend
once within parenthesis like a simpler "(see also <linkend>BAS</>)",
with a set of <literal>s for the follow-up references.
--
Michael
On Thu, Nov 05, 2026 at 05:51:08PM -0800, Lukas Fittl wrote:
Is there a reason we feel that "strategy ring" is the better choice?
If we wanted to make it more clear that its a ring buffer used as part
of a buffer access strategy maybe we can do "strategy ring buffer"?
(or "strategic ring buffer"?)
It's used in the glossary of the documentation on HEAD, at least. I'd
tend to agree that "Buffer Access Strategy ring" would be a more
complete term when not in the context of the glossary page. If the
BAS are explicitely described, like in the glossary, "strategy ring"
reads fine.
--
Michael
Hi,
On Fri, Nov 06, 2026 at 11:14:57AM +0900, Michael Paquier wrote:
On Thu, Nov 05, 2026 at 12:17:42PM +0000, Bertrand Drouvot wrote:
Right, that's why the text refers specifically to the 256KB target. Do you
mean that the L2 cache rationale should be dropped now that it does not
necessarily apply to the resulting ring size?v4 still has the same text:
Yes, your comment was not clear to me, that's why I asked the above question
before proposing changes.
+For bulk reads, the requested ring size is at least 256KB. The 256KB target is +small enough to fit in L2 cache, which makes transferring pages from OS cache +to shared buffer cache efficient.L2 caches can be larger than 256kB, so having a larger size could
still be effective. The current wording sounds slightly confusing to
me here due to that. But perhaps your wording is fine enough. I
suspect that Andres and other folks involved in this area of the code
would have comments about these parts. Better to wait for inputs from
them.
I see what you mean now.
I did some archeology. This wording was added in d526575f893c when L2 larger
than 256kB was already a thing (but the requested ring size did not grow beyond
256kB at the time).
Maybe we could add something like?
"Larger rings may retain this benefit on systems with larger L2 caches"
That said, let's wait for Andres and others as you suggest.
That makes sense. I've listed the commands under each applicable strategy. Note
that some appear in both lists: for example, CREATE DATABASE uses bulkread for
the source and bulkwrite for the destination. I did not go as far as explaining
why in the glossary.The checksumming part is missing for checksums where BAS_VACUUM is in
use.v4-0002 seems mostly OK here, with your previous adjustments included.
+ <literal>vacuum</literal>: I/O operations performed using the + <literal>vacuum</literal> + <glossterm linkend="glossary-buffer-access-strategy">buffer access + strategy</glossterm>
Makes sense, will do.
In v4-0003, too many vacuum references perhaps? This is the same as
my previous comment when I have looked at the v3 series. I'm
wondering if you should make it simpler, by the addition of linkend
once within parenthesis like a simpler "(see also <linkend>BAS</>)",
with a set of <literal>s for the follow-up references.
Good idea, will do.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Hi,
On Fri, Nov 06, 2026 at 11:18:07AM +0900, Michael Paquier wrote:
On Thu, Nov 05, 2026 at 05:51:08PM -0800, Lukas Fittl wrote:
Is there a reason we feel that "strategy ring" is the better choice?
If we wanted to make it more clear that its a ring buffer used as part
of a buffer access strategy maybe we can do "strategy ring buffer"?
(or "strategic ring buffer"?)It's used in the glossary of the documentation on HEAD, at least. I'd
tend to agree that "Buffer Access Strategy ring" would be a more
complete term when not in the context of the glossary page. If the
BAS are explicitely described, like in the glossary, "strategy ring"
reads fine.
That makes sense to me. I'll keep "strategy ring" in the glossary and change the
pg_stat_io wording to "Buffer Access Strategy ring" in v5.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Hi,
On Thu, Nov 05, 2026 at 10:02:13AM -0800, Lukas Fittl wrote:
On Thu, Nov 5, 2026 at 4:18 AM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:0004 extracts the lookup of leader and per-worker table scan instrumentation.
0005 adds optional buffer access strategy instrumentation for table scans and
preserves every strategy observed across rescans.
0006 adds per-worker table scan instrumentation for sample and TID scans.
0007 reports the strategies used by table scans with EXPLAIN (ANALYZE, BUFFERS),
including parallel workers.
0008 reports the strategies used by index, index-only, and bitmap index scans.Hmm. These touch the same area of the code as some of the stuff Lukas
Fittl is working on..Yeah, this may require some rebasing. That said, I think their goals are largely
orthogonal.Yep, from a quick glance I don't think this will require a lot of
coordination, just some rebasing.
Thanks for confirming!
My work is mainly focused on
instrument.c and the executor, vs this here happens mainly in
explain.c and the node-type specific instrumentation. I do also have a
patch set that I haven't posted yet that reworks how we do parallel
worker instrumentation reporting more broadly, but I don't see any
immediate problems that would arise.
Cool, please CC me when you post it so that I don't miss it.
I'm aiming to do a proper review pass on this patchset as well
today/tomorrow, but wanted to comment on that quickly :)
Thanks for doing so!
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Hi,
On Thu, Nov 05, 2026 at 05:51:08PM -0800, Lukas Fittl wrote:
The refactoring in 0004 seems reasonable.
Thanks for the review!
In 0005:
diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index ea3f2a6be99..9ad8dde300f 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -71,8 +71,10 @@ typedef enum ScanOptions /* set if the query doesn't modify the relation */ SO_HINT_REL_READ_ONLY = 1 << 10,- /* collect scan instrumentation */ + /* collect read stream instrumentation */ SO_SCAN_INSTRUMENT = 1 << 11, + /* collect buffer access strategy instrumentation */ + SO_SCAN_INSTRUMENT_STRATEGY = 1 << 12, } ScanOptions;Maybe the revised SO_SCAN_INSTRUMENT comment should read "collect I/O
instrumentation"? One could imagine an alternate table AM that fills
the I/O statistics some other way, or we might add other fields to it
that are not about read streams. This is in line with Tomas noting in
681daed9316 that "The collected stats are designed for ReadStream, but
are meant to be reasonably generic in case a TAM manages I/Os in
different ways.".
Yeah, agreed. Will do in the next revision.
diff --git a/src/include/executor/instrument_node.h b/src/include/executor/instrument_node.h index 0632a72d68c..90216ee07c5 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -18,6 +18,8 @@ #ifndef INSTRUMENT_NODE_H #define INSTRUMENT_NODE_H ... @@ -78,8 +80,35 @@ typedef struct IOStats ... +/* + * Record a buffer access strategy selected by a table AM. + * + * Preserve every strategy observed across rescans. + */ +static inline void +InstrRecordTableScanStrategy(TableScanInstrumentation *instrument, + BufferAccessStrategyType strategy) +{ + if (instrument) + instrument->strategy_mask |= BufferAccessStrategyBit(strategy); +} +I feel the "Instr" prefix is not appropriate here, since this is not
about one of the core instrumentation types defined in instrument.h,
but rather the additional table scan instrumentation, and it'd be the
first method in this file that carries that prefix. What if we just
called this "RecordTableScanStrategy", "RememberTableScanStrategy" or
"SetTableScanStrategy"?
Thanks for the suggested wording. I'll use RecordTableScanStrategy() (and
RecordIndexScanStrategy()) in the next version.
I also wonder a bit if this helper is really needed - it feels like
you might as well just inline that code at the two call sites, and
different from the adjacently used read_stream_enable_stats I don't
really see us adding more code to this helper in the future.
0008 adds the similar InstrRecordIndexScanStrategy() (that I'll rename to
RecordIndexScanStrategy(), see above), used at nine call sites. Keeping
RecordTableScanStrategy() provides consistency, so I think it is worth keeping
despite currently having only two call sites. Does that make sense?
---
0006 seems fine, but would be nice if we refactor some of that
repetition in the future
Yeah, agreed. That repetition already exists and 0006 extends it. Refactoring it
as a follow up makes sense to me (doing it in this patch series would probably
distract from the main purpose).
I'll put a comment in the commit message though.
- I don't have anything staged for that issue
specifically, FWIW, but it reminds me of the repeated code reduction
for parallel index builds at [0].
Yeah, I see the similarity. I'll keep that in mind for the follow up. Thanks for
the pointer!
In 0007:
I wonder a bit if we should tie this to es->verbose (i.e. VERBOSE) as
well and not show it by default. Is this really something that every
user of EXPLAIN ANALYZE always needs to see? (since BUFFERS is now on
by default)For example, we've also made the decision in the 19 cycle to make the
new IO option not be shown by default, and I think that one probably
has a bigger impact on performance optimization for end users than the
buffer access strategy.One doesn't really have much control over the buffer access strategy,
so to me this is more about showing more information on how the system
works internally, which fits VERBOSE well. Reading the original
motivation on why we're adding this, it seems like its more geared
towards Postgres developers, not end users.Alternatively, I'd argue at a minimum we should hide the buffer access
strategy when its "normal". That would still make it verbose for
queries with large sequential scans,
I think that hiding "normal" would be ambiguous, since there are other reasons
for the property to be absent, for example when the scan is not executed, the
leader does not participate, or the AM does not report a strategy.
That said, normal could be reported for many scans (probably most of the time)
and the feature is mostly aimed at developers and advanced users. So, I think I'm
ok with showing it only with VERBOSE.
I'm also a bit skeptical more broadly that 0008 really adds value. How
many people use index AMs that would trigger a bulk read? (it seems
like a bunch of extra code that's unlikely to be needed in practice)
Bloom does use BAS_BULKREAD when scanning the whole index, so there is one
in tree. That said, this single case might not be enough to justify 0008.
FWIW, most of the changes comes from the TAP test. I think it could be reduced
substantially by moving the normal index scan coverage to the existing tests.
So I think that we could keep 0008 in the patch series for now and reduce the
test. 0008 can still be dropped independently if Bloom alone is not considered
sufficient. If we drop it, then I agree that InstrRecordTableScanStrategy()
should be inlined, as you suggested above.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
On Fri, Nov 06, 2026 at 08:14:31AM +0000, Bertrand Drouvot wrote:
Hi,
On Fri, Nov 06, 2026 at 11:14:57AM +0900, Michael Paquier wrote:
On Thu, Nov 05, 2026 at 12:17:42PM +0000, Bertrand Drouvot wrote:
Right, that's why the text refers specifically to the 256KB target. Do you
mean that the L2 cache rationale should be dropped now that it does not
necessarily apply to the resulting ring size?v4 still has the same text:
Yes, your comment was not clear to me, that's why I asked the above question
before proposing changes.+For bulk reads, the requested ring size is at least 256KB. The 256KB target is +small enough to fit in L2 cache, which makes transferring pages from OS cache +to shared buffer cache efficient.L2 caches can be larger than 256kB, so having a larger size could
still be effective. The current wording sounds slightly confusing to
me here due to that. But perhaps your wording is fine enough. I
suspect that Andres and other folks involved in this area of the code
would have comments about these parts. Better to wait for inputs from
them.I see what you mean now.
I did some archeology. This wording was added in d526575f893c when L2 larger
than 256kB was already a thing (but the requested ring size did not grow beyond
256kB at the time).Maybe we could add something like?
"Larger rings may retain this benefit on systems with larger L2 caches"
PFA v5 using the wording proposed above and addressing your other comments. It
also includes the changes discussed with Lukas in [1]/messages/by-id/au2o6TU+qc61264l@bdtpg. I kept 0008 for now,
though we may still decide to drop it.
[1]: /messages/by-id/au2o6TU+qc61264l@bdtpg
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com