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
>From 9efd22beb90200770ac825540b5bb57f59b58985 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 26 Oct 2026 13:50:58 +0000 Subject: [PATCH v3 1/8] Update buffer strategy descriptions in the buffer README The buffer README still describes the bulk-read strategy as using a fixed 256kB ring. Commit 15f0cb26b530 changed this but forgot to update the README: the requested ring size now starts at 256kB and grows to accommodate the configured I/O concurrency, subject to the backend pin limit and the general cap of one eighth of shared_buffers. The actual ring can therefore be smaller than 256kB. The README also says that the bulk-write strategy applies only to COPY IN and CREATE TABLE AS SELECT. It is now used by additional operations, including materialized view population and some table rewrites. Update both descriptions. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- src/backend/storage/buffer/README | 41 ++++++++++++++++++------------- 1 file changed, 24 insertions(+), 17 deletions(-) 100.0% src/backend/storage/buffer/ diff --git a/src/backend/storage/buffer/README b/src/backend/storage/buffer/README index b332e002ba1..430a9ba7a3c 100644 --- a/src/backend/storage/buffer/README +++ b/src/backend/storage/buffer/README @@ -216,19 +216,26 @@ whole scan. This also implies that much of the write traffic caused by such a statement will be done by the backend itself and not pushed off onto other processes. -For sequential scans, a 256KB ring is used. That's small enough to fit in L2 -cache, which makes transferring pages from OS cache to shared buffer cache -efficient. Even less would often be enough, but the ring must be big enough -to accommodate all pages in the scan that are pinned concurrently. 256KB -should also be enough to leave a small cache trail for other backends to -join in a synchronized seq scan. If a ring buffer is dirtied and its LSN -updated, we would normally have to write and flush WAL before we could -re-use the buffer; in this case we instead discard the buffer from the ring -and (later) choose a replacement using the normal clock-sweep algorithm. -Hence this strategy works best for scans that are read-only (or at worst -update hint bits). In a scan that modifies every page in the scan, like a -bulk UPDATE or DELETE, the buffers in the ring will always be dirtied and -the ring strategy effectively degrades to the normal strategy. +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. Even less would often be enough, but the +target must be big enough to accommodate all pages in the scan that are +pinned concurrently. The 256KB target should also be enough to leave a small +cache trail for other backends to join in a synchronized seq scan. + +The requested ring size is enlarged to accommodate io_combine_limit * +effective_io_concurrency buffers while I/O is in progress, but no further +than the backend's pin limit allows. Like all strategy rings, the actual size +is capped at 1/8th of shared_buffers and can therefore be less than 256KB. + +If a ring buffer is dirtied and its LSN updated, we would normally have to +write and flush WAL before we could re-use the buffer; in this case we instead +discard the buffer from the ring and (later) choose a replacement using the +normal clock-sweep algorithm. Hence this strategy works best for scans that +are read-only (or at worst update hint bits). In a scan that modifies every +page in the scan, like a bulk UPDATE or DELETE, the buffers in the ring will +always be dirtied and the ring strategy effectively degrades to the normal +strategy. VACUUM uses a ring like sequential scans, however, the size of this ring is controlled by the vacuum_buffer_usage_limit GUC. Dirty pages are not removed @@ -237,10 +244,10 @@ buffers. Before introducing the buffer ring strategy in 8.3, VACUUM's buffers were sent to the freelist, which was effectively a buffer ring of 1 buffer, resulting in excessive WAL flushing. -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). Smaller sizes have been shown to result in the COPY blocking too often for WAL flushes. While it's okay for a background vacuum to be slowed by doing its own WAL flushing, we'd prefer that COPY not be subject to that, -- 2.34.1
>From 156ab405d6bf7a62dca8b77f7c21ac83c4f64180 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 22 Oct 2026 04:17:46 +0000 Subject: [PATCH v3 2/8] Describe buffer access strategy types in the glossary The glossary explains the purpose and mechanics of buffer access strategies, but not the available strategy types. Describe normal, bulkread, bulkwrite, and vacuum, including the kinds of operations that use each ring-based strategy. Use the types in the places that mentions the glossary. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- doc/src/sgml/config.sgml | 2 ++ doc/src/sgml/glossary.sgml | 49 ++++++++++++++++++++++++---- doc/src/sgml/ref/analyze.sgml | 3 +- doc/src/sgml/ref/vacuum.sgml | 3 +- doc/src/sgml/ref/vacuumdb.sgml | 1 + doc/src/sgml/tablesample-method.sgml | 2 +- 6 files changed, 51 insertions(+), 9 deletions(-) 16.5% doc/src/sgml/ref/ 83.4% doc/src/sgml/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 289058484ab..9b4f4657b44 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2130,6 +2130,7 @@ include_dir 'conf.d' <listitem> <para> Specifies the size of the + <literal>vacuum</literal> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm> used by the <command>VACUUM</command> and <command>ANALYZE</command> commands. A setting of <literal>0</literal> will allow the operation @@ -10142,6 +10143,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; When the failsafe is triggered, any cost-based delay that is in effect will no longer be applied, further non-essential 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 free to make use of all of diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index d28a3729188..890c6998eb2 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -329,12 +329,49 @@ <glossterm linkend="glossary-wal">WAL</glossterm> to permanent storage. </para> <para> - 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> + The available buffer access strategy types are: + </para> + <variablelist> + <varlistentry> + <term><literal>normal</literal></term> + <listitem> + <para> + Uses ordinary buffer replacement rather than a strategy ring. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>bulkread</literal></term> + <listitem> + <para> + Uses a strategy ring for operations expected to read many pages with + little reuse, such as large sequential scans. Reusing a limited set of + buffers reduces eviction of unrelated pages from shared buffers. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>bulkwrite</literal></term> + <listitem> + <para> + Uses a strategy ring for operations that write many pages, such as + <command>COPY FROM</command> and + <command>CREATE TABLE AS SELECT</command>. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><literal>vacuum</literal></term> + <listitem> + <para> + Uses a strategy ring for maintenance operations such as + <command>VACUUM</command> and <command>ANALYZE</command>. Its size can + be controlled with + <xref linkend="guc-vacuum-buffer-usage-limit"/>. + </para> + </listitem> + </varlistentry> + </variablelist> </glossdef> </glossentry> diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index d27c0d9e864..671c5cca8a4 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -94,10 +94,11 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r <listitem> <para> 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 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>. When this option is not specified, <command>ANALYZE</command> uses the value from <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 25234a17baf..78ec7195946 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -314,10 +314,11 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re <listitem> <para> Specifies the + <literal>vacuum</literal> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm> 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> is also specified, the <option>BUFFER_USAGE_LIMIT</option> value is used for both the vacuum and analyze stages. This option can't be used with diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 508c8dfa146..cabc816837c 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -136,6 +136,7 @@ PostgreSQL documentation <listitem> <para> Specifies the + <literal>vacuum</literal> <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm> ring buffer size for a given invocation of <application>vacuumdb</application>. This size is used to calculate the number of shared buffers which will diff --git a/doc/src/sgml/tablesample-method.sgml b/doc/src/sgml/tablesample-method.sgml index c821941b71b..8465005a3d7 100644 --- a/doc/src/sgml/tablesample-method.sgml +++ b/doc/src/sgml/tablesample-method.sgml @@ -202,7 +202,7 @@ BeginSampleScan (SampleScanState *node, This function may adjust the fields <literal>node->use_bulkread</literal> and <literal>node->use_pagemode</literal>. If <literal>node->use_bulkread</literal> is <literal>true</literal>, which it is by - default, the scan will use a buffer access strategy that encourages + default, the scan will use a <literal>bulkread</literal> buffer access strategy that encourages recycling buffers after use. It might be reasonable to set this to <literal>false</literal> if the scan will visit only a small fraction of the table's pages. -- 2.34.1
>From aa11573cd03f972be7a0a18dfe92cff9a4a8549d Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 09:42:48 +0000 Subject: [PATCH v3 3/8] Clarify buffer access strategies in pg_stat_io documentation The pg_stat_io descriptions for vacuum, bulkread, and bulkwrite say that I/O is performed outside shared buffers, and the evictions and reuses descriptions refer to a separate ring buffer. Strategy rings instead contain references to a limited set of shared buffers. Identify each context with its same named buffer access strategy and remove the incorrect references to strategy rings as separate from shared buffers. Suggested-by: Sami Imseih <[email protected]> Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- doc/src/sgml/monitoring.sgml | 34 ++++++++++++++++++++-------------- 1 file changed, 20 insertions(+), 14 deletions(-) 100.0% doc/src/sgml/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 445fbc9232c..c105ed29771 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -3144,24 +3144,30 @@ description | Waiting for a newly initialized WAL file to reach durable storage </listitem> <listitem> <para> - <literal>vacuum</literal>: I/O operations performed outside of shared - buffers 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>. + <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>. </para> </listitem> <listitem> <para> <literal>bulkread</literal>: Certain large read I/O operations - done outside of shared buffers, for example, a sequential scan of a - large table. + performed using the <literal>bulkread</literal> + <glossterm linkend="glossary-buffer-access-strategy">buffer access + strategy</glossterm>, for example, a sequential scan of a large + table. </para> </listitem> <listitem> <para> <literal>bulkwrite</literal>: Certain large write I/O operations - done outside of shared buffers, such as <command>COPY</command>. + performed using the <literal>bulkwrite</literal> + <glossterm linkend="glossary-buffer-access-strategy">buffer access + strategy</glossterm>, such as <command>COPY FROM</command>. </para> </listitem> </itemizedlist> @@ -3340,8 +3346,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> @@ -3352,10 +3358,10 @@ description | Waiting for a newly initialized WAL file to reach durable storage <structfield>reuses</structfield> <type>bigint</type> </para> <para> - The number of times an existing buffer in a size-limited ring buffer - outside of shared buffers was reused as part of an I/O operation in the - <literal>bulkread</literal>, <literal>bulkwrite</literal>, or - <literal>vacuum</literal> <varname>context</varname>s. + The number of times a shared buffer in a strategy ring was reused as + part of an I/O operation in the <literal>bulkread</literal>, + <literal>bulkwrite</literal>, or <literal>vacuum</literal> + <varname>context</varname>s. </para> </entry> </row> -- 2.34.1
>From cae2742abe11c1c48a7108b7b26badb82906cfbe Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 26 Oct 2026 15:34:27 +0000 Subject: [PATCH v3 4/8] Refactor table scan instrumentation lookup Extract the lookup of leader and per-worker table scan instrumentation from show_scan_io_usage(). This will allow other scan instrumentation to reuse the lookup without coupling it to I/O reporting. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- src/backend/commands/explain.c | 158 +++++++++++++++++---------------- 1 file changed, 80 insertions(+), 78 deletions(-) 100.0% src/backend/commands/ diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 4b1ad4227ac..d0ce72d83ff 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -4088,112 +4088,114 @@ print_io_usage(ExplainState *es, IOStats *stats) } /* - * Show information about prefetch and I/O in a scan node. + * Return table scan instrumentation for the leader or a parallel worker. + * + * Pass a negative worker number for the leader. Return NULL if the process + * has no scan instrumentation or the node type does not collect it. */ -static void -show_scan_io_usage(ScanState *planstate, ExplainState *es) +static TableScanInstrumentation * +get_table_scan_instrumentation(ScanState *planstate, int worker) { - Plan *plan = planstate->ps.plan; - IOStats stats = {0}; - - if (!es->io) - return; - - /* - * Initialize counters with stats from the local process first. - * - * The scan descriptor may not exist, e.g. if the scan did not start, or - * because of debug_parallel_query=regress. We still want to collect data - * from workers. - */ - if (planstate->ss_currentScanDesc && - planstate->ss_currentScanDesc->rs_instrument) + if (worker < 0) { - stats = planstate->ss_currentScanDesc->rs_instrument->io; + if (planstate->ss_currentScanDesc) + return planstate->ss_currentScanDesc->rs_instrument; + + return NULL; } - /* - * Accumulate data from parallel workers (if any). - */ - switch (nodeTag(plan)) + switch (nodeTag(planstate->ps.plan)) { case T_BitmapHeapScan: { - SharedBitmapHeapInstrumentation *sinstrument - = ((BitmapHeapScanState *) planstate)->sinstrument; - - if (sinstrument) - { - for (int i = 0; i < sinstrument->num_workers; ++i) - { - BitmapHeapScanInstrumentation *winstrument = &sinstrument->sinstrument[i]; - - AccumulateIOStats(&stats, &winstrument->stats.io); - - if (!es->workers_state) - continue; - - ExplainOpenWorker(i, es); - print_io_usage(es, &winstrument->stats.io); - ExplainCloseWorker(i, es); - } - } + SharedBitmapHeapInstrumentation *sinstrument = + ((BitmapHeapScanState *) planstate)->sinstrument; + if (sinstrument && worker < sinstrument->num_workers) + return &sinstrument->sinstrument[worker].stats; break; } case T_SeqScan: { - SharedSeqScanInstrumentation *sinstrument - = ((SeqScanState *) planstate)->sinstrument; - - if (sinstrument) - { - for (int i = 0; i < sinstrument->num_workers; ++i) - { - SeqScanInstrumentation *winstrument = &sinstrument->sinstrument[i]; - - AccumulateIOStats(&stats, &winstrument->stats.io); - - if (!es->workers_state) - continue; - - ExplainOpenWorker(i, es); - print_io_usage(es, &winstrument->stats.io); - ExplainCloseWorker(i, es); - } - } + SharedSeqScanInstrumentation *sinstrument = + ((SeqScanState *) planstate)->sinstrument; + if (sinstrument && worker < sinstrument->num_workers) + return &sinstrument->sinstrument[worker].stats; break; } case T_TidRangeScan: { - SharedTidRangeScanInstrumentation *sinstrument - = ((TidRangeScanState *) planstate)->trss_sinstrument; + SharedTidRangeScanInstrumentation *sinstrument = + ((TidRangeScanState *) planstate)->trss_sinstrument; - if (sinstrument) - { - for (int i = 0; i < sinstrument->num_workers; ++i) - { - TidRangeScanInstrumentation *winstrument = &sinstrument->sinstrument[i]; + if (sinstrument && worker < sinstrument->num_workers) + return &sinstrument->sinstrument[worker].stats; + break; + } + default: + break; + } - AccumulateIOStats(&stats, &winstrument->stats.io); + return NULL; +} - if (!es->workers_state) - continue; +/* + * Show information about prefetch and I/O in a scan node. + */ +static void +show_scan_io_usage(ScanState *planstate, ExplainState *es) +{ + TableScanInstrumentation *instrument; + IOStats stats = {0}; - ExplainOpenWorker(i, es); - print_io_usage(es, &winstrument->stats.io); - ExplainCloseWorker(i, es); - } - } + if (!es->io) + return; - break; - } + /* I/O usage is reported only for these scan types */ + switch (nodeTag(planstate->ps.plan)) + { + case T_BitmapHeapScan: + case T_SeqScan: + case T_TidRangeScan: + break; default: /* ignore other plans */ return; } + /* + * Initialize counters with stats from the local process first. + * + * The scan descriptor may not exist, e.g. if the scan did not start, or + * because of debug_parallel_query=regress. We still want to collect data + * from workers. + */ + instrument = get_table_scan_instrumentation(planstate, -1); + if (instrument) + stats = instrument->io; + + /* + * Accumulate data from parallel workers (if any). + */ + for (int i = 0;; ++i) + { + TableScanInstrumentation *winstrument = + get_table_scan_instrumentation(planstate, i); + + if (!winstrument) + break; + + AccumulateIOStats(&stats, &winstrument->io); + + if (!es->workers_state) + continue; + + ExplainOpenWorker(i, es); + print_io_usage(es, &winstrument->io); + ExplainCloseWorker(i, es); + } + print_io_usage(es, &stats); } -- 2.34.1
>From a275441d38efdc0fa9ce4324cd8a20ba3982d60d Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 06:49:11 +0000 Subject: [PATCH v3 5/8] Add buffer access strategies to table scan instrumentation Extend table scan instrumentation with optional buffer access strategies and have the heap table AM record them when requested. Store the observed strategies in a bitmask so rescans preserve every selected strategy. An empty mask allows another table AM to leave the information unavailable. Propagate the strategies through the existing parallel worker instrumentation for sequential, bitmap heap, and TID range scans. Move BufferAccessStrategyType to buf.h so executor instrumentation can use it without including the heavier bufmgr.h. This provides the common infrastructure for EXPLAIN reporting added in a following commit. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- src/backend/access/heap/heapam.c | 20 ++++++++--- src/backend/executor/nodeBitmapHeapscan.c | 10 ++++-- src/backend/executor/nodeSeqscan.c | 18 +++++++--- src/backend/executor/nodeTidrangescan.c | 18 +++++++--- src/backend/storage/buffer/freelist.c | 14 ++++++++ src/include/access/tableam.h | 4 ++- src/include/executor/instrument_node.h | 42 +++++++++++++++++++++++ src/include/storage/buf.h | 15 ++++++++ src/include/storage/bufmgr.h | 16 +-------- 9 files changed, 124 insertions(+), 33 deletions(-) 14.7% src/backend/access/heap/ 42.4% src/backend/executor/ 4.9% src/backend/storage/buffer/ 17.6% src/include/executor/ 17.3% src/include/storage/ diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 6a09a1a1517..ce49ab07ff8 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -1317,12 +1317,20 @@ heap_beginscan(Relation relation, Snapshot snapshot, sizeof(TBMIterateResult)); } - /* enable read stream instrumentation */ - if ((flags & SO_SCAN_INSTRUMENT) && (scan->rs_read_stream != NULL)) + /* enable scan instrumentation */ + if (flags & (SO_SCAN_INSTRUMENT | SO_SCAN_INSTRUMENT_STRATEGY)) { scan->rs_base.rs_instrument = palloc0_object(TableScanInstrumentation); - read_stream_enable_stats(scan->rs_read_stream, - &scan->rs_base.rs_instrument->io); + + /* strategy instrumentation */ + if (flags & SO_SCAN_INSTRUMENT_STRATEGY) + InstrRecordTableScanStrategy(scan->rs_base.rs_instrument, + GetAccessStrategyType(scan->rs_strategy)); + + /* read stream instrumentation */ + if ((flags & SO_SCAN_INSTRUMENT) && scan->rs_read_stream != NULL) + read_stream_enable_stats(scan->rs_read_stream, + &scan->rs_base.rs_instrument->io); } scan->rs_vmbuffer = InvalidBuffer; @@ -1387,6 +1395,10 @@ heap_rescan(TableScanDesc sscan, ScanKey key, bool set_params, * reinitialize scan descriptor */ initscan(scan, key, true); + + if (scan->rs_base.rs_flags & SO_SCAN_INSTRUMENT_STRATEGY) + InstrRecordTableScanStrategy(scan->rs_base.rs_instrument, + GetAccessStrategyType(scan->rs_strategy)); } void diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c index a9e83f30687..3412ad21e52 100644 --- a/src/backend/executor/nodeBitmapHeapscan.c +++ b/src/backend/executor/nodeBitmapHeapscan.c @@ -149,6 +149,8 @@ BitmapTableScanSetup(BitmapHeapScanState *node) if (node->ss.ps.state->es_instrument & INSTRUMENT_IO) flags |= SO_SCAN_INSTRUMENT; + if (node->ss.ps.state->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; node->ss.ss_currentScanDesc = table_beginscan_bm(node->ss.ss_currentRelation, @@ -334,12 +336,14 @@ ExecEndBitmapHeapScan(BitmapHeapScanState *node) si->exact_pages += node->stats.exact_pages; si->lossy_pages += node->stats.lossy_pages; - /* collect I/O instrumentation for this process */ + /* Collect scan instrumentation for this process */ if (node->ss.ss_currentScanDesc && node->ss.ss_currentScanDesc->rs_instrument) { - AccumulateIOStats(&si->stats.io, - &node->ss.ss_currentScanDesc->rs_instrument->io); + TableScanInstrumentation *stats = + node->ss.ss_currentScanDesc->rs_instrument; + + CombineTableScanInstrumentation(&si->stats, stats); } } diff --git a/src/backend/executor/nodeSeqscan.c b/src/backend/executor/nodeSeqscan.c index b8c528ca089..96f62451240 100644 --- a/src/backend/executor/nodeSeqscan.c +++ b/src/backend/executor/nodeSeqscan.c @@ -73,6 +73,8 @@ SeqNext(SeqScanState *node) if (estate->es_instrument & INSTRUMENT_IO) flags |= SO_SCAN_INSTRUMENT; + if (estate->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; /* * We reach here if the scan is not parallel, or if we're serially @@ -310,7 +312,7 @@ ExecEndSeqScan(SeqScanState *node) scanDesc = node->ss.ss_currentScanDesc; /* - * Collect I/O stats for this process into shared instrumentation. + * Collect scan statistics for this process into shared instrumentation. */ if (node->sinstrument != NULL && IsParallelWorker()) { @@ -321,7 +323,7 @@ ExecEndSeqScan(SeqScanState *node) if (scanDesc && scanDesc->rs_instrument) { - AccumulateIOStats(&si->stats.io, &scanDesc->rs_instrument->io); + CombineTableScanInstrumentation(&si->stats, scanDesc->rs_instrument); } } @@ -400,6 +402,8 @@ ExecSeqScanInitializeDSM(SeqScanState *node, if (estate->es_instrument & INSTRUMENT_IO) flags |= SO_SCAN_INSTRUMENT; + if (estate->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; pscan = shm_toc_allocate(pcxt->toc, node->pscan_len); table_parallelscan_initialize(node->ss.ss_currentRelation, @@ -445,6 +449,8 @@ ExecSeqScanInitializeWorker(SeqScanState *node, if (node->ss.ps.state->es_instrument & INSTRUMENT_IO) flags |= SO_SCAN_INSTRUMENT; + if (node->ss.ps.state->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; pscan = shm_toc_lookup(pwcxt->toc, node->ss.ps.plan->plan_node_id, false); node->ss.ss_currentScanDesc = @@ -461,7 +467,8 @@ ExecSeqScanInstrumentEstimate(SeqScanState *node, ParallelContext *pcxt) EState *estate = node->ss.ps.state; Size size; - if ((estate->es_instrument & INSTRUMENT_IO) == 0 || pcxt->nworkers == 0) + if ((estate->es_instrument & (INSTRUMENT_BUFFERS | INSTRUMENT_IO)) == 0 || + pcxt->nworkers == 0) return; size = add_size(offsetof(SharedSeqScanInstrumentation, sinstrument), @@ -481,7 +488,8 @@ ExecSeqScanInstrumentInitDSM(SeqScanState *node, ParallelContext *pcxt) SharedSeqScanInstrumentation *sinstrument; Size size; - if ((estate->es_instrument & INSTRUMENT_IO) == 0 || pcxt->nworkers == 0) + if ((estate->es_instrument & (INSTRUMENT_BUFFERS | INSTRUMENT_IO)) == 0 || + pcxt->nworkers == 0) return; size = add_size(offsetof(SharedSeqScanInstrumentation, sinstrument), @@ -505,7 +513,7 @@ ExecSeqScanInstrumentInitWorker(SeqScanState *node, { EState *estate = node->ss.ps.state; - if ((estate->es_instrument & INSTRUMENT_IO) == 0) + if ((estate->es_instrument & (INSTRUMENT_BUFFERS | INSTRUMENT_IO)) == 0) return; node->sinstrument = shm_toc_lookup(pwcxt->toc, diff --git a/src/backend/executor/nodeTidrangescan.c b/src/backend/executor/nodeTidrangescan.c index b387ed6c308..597e0288f3b 100644 --- a/src/backend/executor/nodeTidrangescan.c +++ b/src/backend/executor/nodeTidrangescan.c @@ -251,6 +251,8 @@ TidRangeNext(TidRangeScanState *node) if (estate->es_instrument & INSTRUMENT_IO) flags |= SO_SCAN_INSTRUMENT; + if (estate->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; scandesc = table_beginscan_tidrange(node->ss.ss_currentRelation, estate->es_snapshot, @@ -351,7 +353,7 @@ ExecEndTidRangeScan(TidRangeScanState *node) { TableScanDesc scan = node->ss.ss_currentScanDesc; - /* Collect IO stats for this process into shared instrumentation */ + /* Collect scan statistics for this process into shared instrumentation */ if (node->trss_sinstrument != NULL && IsParallelWorker()) { TidRangeScanInstrumentation *si; @@ -361,7 +363,7 @@ ExecEndTidRangeScan(TidRangeScanState *node) if (scan && scan->rs_instrument) { - AccumulateIOStats(&si->stats.io, &scan->rs_instrument->io); + CombineTableScanInstrumentation(&si->stats, scan->rs_instrument); } } @@ -484,6 +486,8 @@ ExecTidRangeScanInitializeDSM(TidRangeScanState *node, ParallelContext *pcxt) if (estate->es_instrument & INSTRUMENT_IO) flags |= SO_SCAN_INSTRUMENT; + if (estate->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; pscan = shm_toc_allocate(pcxt->toc, node->trss_pscanlen); table_parallelscan_initialize(node->ss.ss_currentRelation, @@ -529,6 +533,8 @@ ExecTidRangeScanInitializeWorker(TidRangeScanState *node, if (node->ss.ps.state->es_instrument & INSTRUMENT_IO) flags |= SO_SCAN_INSTRUMENT; + if (node->ss.ps.state->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; pscan = shm_toc_lookup(pwcxt->toc, node->ss.ps.plan->plan_node_id, false); node->ss.ss_currentScanDesc = @@ -547,7 +553,8 @@ ExecTidRangeScanInstrumentEstimate(TidRangeScanState *node, EState *estate = node->ss.ps.state; Size size; - if ((estate->es_instrument & INSTRUMENT_IO) == 0 || pcxt->nworkers == 0) + if ((estate->es_instrument & (INSTRUMENT_BUFFERS | INSTRUMENT_IO)) == 0 || + pcxt->nworkers == 0) return; size = add_size(offsetof(SharedTidRangeScanInstrumentation, sinstrument), @@ -568,7 +575,8 @@ ExecTidRangeScanInstrumentInitDSM(TidRangeScanState *node, SharedTidRangeScanInstrumentation *sinstrument; Size size; - if ((estate->es_instrument & INSTRUMENT_IO) == 0 || pcxt->nworkers == 0) + if ((estate->es_instrument & (INSTRUMENT_BUFFERS | INSTRUMENT_IO)) == 0 || + pcxt->nworkers == 0) return; size = add_size(offsetof(SharedTidRangeScanInstrumentation, sinstrument), @@ -592,7 +600,7 @@ ExecTidRangeScanInstrumentInitWorker(TidRangeScanState *node, { EState *estate = node->ss.ps.state; - if ((estate->es_instrument & INSTRUMENT_IO) == 0) + if ((estate->es_instrument & (INSTRUMENT_BUFFERS | INSTRUMENT_IO)) == 0) return; node->trss_sinstrument = shm_toc_lookup(pwcxt->toc, diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c index f4b5f2cf44e..57dba7a893e 100644 --- a/src/backend/storage/buffer/freelist.c +++ b/src/backend/storage/buffer/freelist.c @@ -542,6 +542,20 @@ GetAccessStrategyWithSize(BufferAccessStrategyType btype, int ring_size_kb) return strategy; } +/* + * GetAccessStrategyType -- return the type of a buffer access strategy. + * + * A NULL strategy represents normal buffer replacement. + */ +BufferAccessStrategyType +GetAccessStrategyType(BufferAccessStrategy strategy) +{ + if (strategy == NULL) + return BAS_NORMAL; + + return strategy->btype; +} + /* * GetAccessStrategyBufferCount -- an accessor for the number of buffers in * the ring 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; /* 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 +#include "storage/buf.h" + /* * Offset added to plan_node_id to create a second TOC key for per-worker scan * instrumentation. Instrumentation and parallel-awareness are independent, so @@ -78,8 +80,35 @@ typedef struct IOStats typedef struct TableScanInstrumentation { IOStats io; + + /* Buffer access strategies reported by the table AM. */ + uint32 strategy_mask; } TableScanInstrumentation; +/* + * Return the instrumentation bit for a buffer access strategy. + */ +static inline uint32 +BufferAccessStrategyBit(BufferAccessStrategyType strategy) +{ + Assert(strategy >= BAS_NORMAL && strategy <= BAS_VACUUM); + + return 1U << strategy; +} + +/* + * 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); +} + /* merge IO statistics from 'src' into 'dst' */ static inline void AccumulateIOStats(IOStats *dst, IOStats *src) @@ -96,6 +125,19 @@ AccumulateIOStats(IOStats *dst, IOStats *src) dst->io_in_progress += src->io_in_progress; } +/* + * Combine table scan instrumentation from 'src' into 'dst'. + * + * I/O statistics are accumulated and observed strategies are preserved. + */ +static inline void +CombineTableScanInstrumentation(TableScanInstrumentation *dst, + TableScanInstrumentation *src) +{ + AccumulateIOStats(&dst->io, &src->io); + dst->strategy_mask |= src->strategy_mask; +} + /* --------------------- * Instrumentation information for index scans (used by all AM interfaces) diff --git a/src/include/storage/buf.h b/src/include/storage/buf.h index b21445522b1..06583a253fd 100644 --- a/src/include/storage/buf.h +++ b/src/include/storage/buf.h @@ -36,6 +36,21 @@ typedef int Buffer; */ #define BufferIsLocal(buffer) ((buffer) < 0) +/* + * Possible arguments for GetAccessStrategy(). + * + * If adding a new BufferAccessStrategyType, also add a new IOContext so + * IO statistics using this strategy are tracked. + */ +typedef enum BufferAccessStrategyType +{ + BAS_NORMAL, /* Normal random access */ + BAS_BULKREAD, /* Large read-only scan (hint bit updates are + * ok) */ + BAS_BULKWRITE, /* Large multi-block write (e.g. COPY IN) */ + BAS_VACUUM, /* VACUUM */ +} BufferAccessStrategyType; + /* * Buffer access strategy objects. * diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 6837b35fc6d..be45f000982 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -25,21 +25,6 @@ typedef void *Block; -/* - * Possible arguments for GetAccessStrategy(). - * - * If adding a new BufferAccessStrategyType, also add a new IOContext so - * IO statistics using this strategy are tracked. - */ -typedef enum BufferAccessStrategyType -{ - BAS_NORMAL, /* Normal random access */ - BAS_BULKREAD, /* Large read-only scan (hint bit updates are - * ok) */ - BAS_BULKWRITE, /* Large multi-block write (e.g. COPY IN) */ - BAS_VACUUM, /* VACUUM */ -} BufferAccessStrategyType; - /* Possible modes for ReadBufferExtended() */ typedef enum { @@ -379,6 +364,7 @@ extern void AtProcExit_LocalBuffers(void); extern BufferAccessStrategy GetAccessStrategy(BufferAccessStrategyType btype); extern BufferAccessStrategy GetAccessStrategyWithSize(BufferAccessStrategyType btype, int ring_size_kb); +extern BufferAccessStrategyType GetAccessStrategyType(BufferAccessStrategy strategy); extern int GetAccessStrategyBufferCount(BufferAccessStrategy strategy); extern int GetAccessStrategyPinLimit(BufferAccessStrategy strategy); -- 2.34.1
>From bc28a756788bd742a40bdd2bacf914b13ca27b05 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 06:49:21 +0000 Subject: [PATCH v3 6/8] Add table scan instrumentation for sample and TID scans Sample and TID scans may run entirely in a worker, but they do not have the per worker table scan instrumentation used by sequential, bitmap heap, and TID range scans. Add the same DSM estimate, initialization, worker attachment, and retrieval handling for these nodes. Allow their scan descriptors to request table scan instrumentation and record it during scan setup. This lets callers retrieve leader and worker scan instrumentation consistently. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- src/backend/executor/execParallel.c | 36 ++++++++ src/backend/executor/nodeSamplescan.c | 112 +++++++++++++++++++++++- src/backend/executor/nodeTidscan.c | 114 ++++++++++++++++++++++++- src/backend/utils/adt/tid.c | 2 +- src/include/access/tableam.h | 8 +- src/include/executor/instrument_node.h | 37 ++++++++ src/include/executor/nodeSamplescan.h | 10 +++ src/include/executor/nodeTidscan.h | 10 +++ src/include/nodes/execnodes.h | 2 + src/tools/pgindent/typedefs.list | 4 + 10 files changed, 324 insertions(+), 11 deletions(-) 75.5% src/backend/executor/ 3.3% src/include/access/ 17.9% src/include/executor/ 3.1% src/ diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c index 38549fd4ab9..9706b04e7e0 100644 --- a/src/backend/executor/execParallel.c +++ b/src/backend/executor/execParallel.c @@ -38,9 +38,11 @@ #include "executor/nodeIndexscan.h" #include "executor/nodeMaterial.h" #include "executor/nodeMemoize.h" +#include "executor/nodeSamplescan.h" #include "executor/nodeSeqscan.h" #include "executor/nodeSort.h" #include "executor/nodeSubplan.h" +#include "executor/nodeTidscan.h" #include "executor/nodeTidrangescan.h" #include "executor/tqueue.h" #include "jit/jit.h" @@ -262,6 +264,11 @@ ExecParallelEstimate(PlanState *planstate, ExecParallelEstimateContext *e) ExecSeqScanInstrumentEstimate((SeqScanState *) planstate, e->pcxt); break; + case T_SampleScanState: + /* even when not parallel-aware, for EXPLAIN ANALYZE */ + ExecSampleScanInstrumentEstimate((SampleScanState *) planstate, + e->pcxt); + break; case T_IndexScanState: if (planstate->plan->parallel_aware) ExecIndexScanEstimate((IndexScanState *) planstate, @@ -288,6 +295,10 @@ ExecParallelEstimate(PlanState *planstate, ExecParallelEstimateContext *e) ExecForeignScanEstimate((ForeignScanState *) planstate, e->pcxt); break; + case T_TidScanState: + /* even when not parallel-aware, for EXPLAIN ANALYZE */ + ExecTidScanInstrumentEstimate((TidScanState *) planstate, e->pcxt); + break; case T_TidRangeScanState: if (planstate->plan->parallel_aware) ExecTidRangeScanEstimate((TidRangeScanState *) planstate, @@ -515,6 +526,11 @@ ExecParallelInitializeDSM(PlanState *planstate, ExecSeqScanInstrumentInitDSM((SeqScanState *) planstate, d->pcxt); break; + case T_SampleScanState: + /* even when not parallel-aware, for EXPLAIN ANALYZE */ + ExecSampleScanInstrumentInitDSM((SampleScanState *) planstate, + d->pcxt); + break; case T_IndexScanState: if (planstate->plan->parallel_aware) ExecIndexScanInitializeDSM((IndexScanState *) planstate, @@ -540,6 +556,10 @@ ExecParallelInitializeDSM(PlanState *planstate, ExecForeignScanInitializeDSM((ForeignScanState *) planstate, d->pcxt); break; + case T_TidScanState: + /* even when not parallel-aware, for EXPLAIN ANALYZE */ + ExecTidScanInstrumentInitDSM((TidScanState *) planstate, d->pcxt); + break; case T_TidRangeScanState: if (planstate->plan->parallel_aware) ExecTidRangeScanInitializeDSM((TidRangeScanState *) planstate, @@ -1174,6 +1194,12 @@ ExecParallelRetrieveInstrumentation(PlanState *planstate, case T_SeqScanState: ExecSeqScanRetrieveInstrumentation((SeqScanState *) planstate); break; + case T_SampleScanState: + ExecSampleScanRetrieveInstrumentation((SampleScanState *) planstate); + break; + case T_TidScanState: + ExecTidScanRetrieveInstrumentation((TidScanState *) planstate); + break; case T_TidRangeScanState: ExecTidRangeScanRetrieveInstrumentation((TidRangeScanState *) planstate); break; @@ -1420,6 +1446,11 @@ ExecParallelInitializeWorker(PlanState *planstate, ParallelWorkerContext *pwcxt) /* even when not parallel-aware, for EXPLAIN ANALYZE */ ExecSeqScanInstrumentInitWorker((SeqScanState *) planstate, pwcxt); break; + case T_SampleScanState: + /* even when not parallel-aware, for EXPLAIN ANALYZE */ + ExecSampleScanInstrumentInitWorker((SampleScanState *) planstate, + pwcxt); + break; case T_IndexScanState: if (planstate->plan->parallel_aware) ExecIndexScanInitializeWorker((IndexScanState *) planstate, @@ -1446,6 +1477,11 @@ ExecParallelInitializeWorker(PlanState *planstate, ParallelWorkerContext *pwcxt) ExecForeignScanInitializeWorker((ForeignScanState *) planstate, pwcxt); break; + case T_TidScanState: + /* even when not parallel-aware, for EXPLAIN ANALYZE */ + ExecTidScanInstrumentInitWorker((TidScanState *) planstate, + pwcxt); + break; case T_TidRangeScanState: if (planstate->plan->parallel_aware) ExecTidRangeScanInitializeWorker((TidRangeScanState *) planstate, diff --git a/src/backend/executor/nodeSamplescan.c b/src/backend/executor/nodeSamplescan.c index f3d273e1c5e..2bdebb81293 100644 --- a/src/backend/executor/nodeSamplescan.c +++ b/src/backend/executor/nodeSamplescan.c @@ -18,7 +18,9 @@ #include "access/tableam.h" #include "access/tsmapi.h" #include "common/pg_prng.h" +#include "executor/execParallel.h" #include "executor/executor.h" +#include "executor/instrument.h" #include "executor/nodeSamplescan.h" #include "utils/fmgrprotos.h" #include "utils/rel.h" @@ -179,17 +181,31 @@ ExecInitSampleScan(SampleScan *node, EState *estate, int eflags) void ExecEndSampleScan(SampleScanState *node) { + TableScanDesc scan = node->ss.ss_currentScanDesc; + /* * Tell sampling function that we finished the scan. */ if (node->tsmroutine->EndSampleScan) node->tsmroutine->EndSampleScan(node); + /* Collect scan statistics for this process into shared instrumentation */ + if (node->sinstrument != NULL && IsParallelWorker()) + { + SampleScanInstrumentation *si; + + Assert(ParallelWorkerNumber < node->sinstrument->num_workers); + si = &node->sinstrument->sinstrument[ParallelWorkerNumber]; + + if (scan && scan->rs_instrument) + CombineTableScanInstrumentation(&si->stats, scan->rs_instrument); + } + /* * close heap scan */ - if (node->ss.ss_currentScanDesc) - table_endscan(node->ss.ss_currentScanDesc); + if (scan) + table_endscan(scan); } /* ---------------------------------------------------------------- @@ -211,6 +227,89 @@ ExecReScanSampleScan(SampleScanState *node) ExecScanReScan(&node->ss); } +/* + * Compute the amount of space needed for shared sample scan instrumentation. + */ +void +ExecSampleScanInstrumentEstimate(SampleScanState *node, ParallelContext *pcxt) +{ + EState *estate = node->ss.ps.state; + Size size; + + if ((estate->es_instrument & INSTRUMENT_BUFFERS) == 0 || pcxt->nworkers == 0) + return; + + size = add_size(offsetof(SharedSampleScanInstrumentation, sinstrument), + mul_size(pcxt->nworkers, sizeof(SampleScanInstrumentation))); + + shm_toc_estimate_chunk(&pcxt->estimator, size); + shm_toc_estimate_keys(&pcxt->estimator, 1); +} + +/* + * Set up shared sample scan instrumentation. + */ +void +ExecSampleScanInstrumentInitDSM(SampleScanState *node, ParallelContext *pcxt) +{ + EState *estate = node->ss.ps.state; + SharedSampleScanInstrumentation *sinstrument; + Size size; + + if ((estate->es_instrument & INSTRUMENT_BUFFERS) == 0 || + pcxt->nworkers == 0) + return; + + size = add_size(offsetof(SharedSampleScanInstrumentation, sinstrument), + mul_size(pcxt->nworkers, sizeof(SampleScanInstrumentation))); + + sinstrument = shm_toc_allocate(pcxt->toc, size); + memset(sinstrument, 0, size); + sinstrument->num_workers = pcxt->nworkers; + + shm_toc_insert(pcxt->toc, node->ss.ps.plan->plan_node_id + + PARALLEL_KEY_SCAN_INSTRUMENT_OFFSET, + sinstrument); + + node->sinstrument = sinstrument; +} + +/* + * Look up the shared sample scan instrumentation in a parallel worker. + */ +void +ExecSampleScanInstrumentInitWorker(SampleScanState *node, ParallelWorkerContext *pwcxt) +{ + EState *estate = node->ss.ps.state; + + if ((estate->es_instrument & INSTRUMENT_BUFFERS) == 0) + return; + + node->sinstrument = shm_toc_lookup(pwcxt->toc, + node->ss.ps.plan->plan_node_id + + PARALLEL_KEY_SCAN_INSTRUMENT_OFFSET, + false); +} + +/* + * Transfer sample scan instrumentation from DSM to private memory. + */ +void +ExecSampleScanRetrieveInstrumentation(SampleScanState *node) +{ + SharedSampleScanInstrumentation *sinstrument = node->sinstrument; + Size size; + + if (sinstrument == NULL) + return; + + size = offsetof(SharedSampleScanInstrumentation, sinstrument) + + sinstrument->num_workers * sizeof(SampleScanInstrumentation); + + node->sinstrument = palloc(size); + memcpy(node->sinstrument, sinstrument, size); +} + /* * Initialize the TABLESAMPLE method: evaluate params and call BeginSampleScan. @@ -292,6 +391,12 @@ tablesample_init(SampleScanState *scanstate) /* Now we can create or reset the HeapScanDesc */ if (scanstate->ss.ss_currentScanDesc == NULL) { + uint32 flags = ScanRelIsReadOnly(&scanstate->ss) ? + SO_HINT_REL_READ_ONLY : SO_NONE; + + if (scanstate->ss.ps.state->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; + scanstate->ss.ss_currentScanDesc = table_beginscan_sampling(scanstate->ss.ss_currentRelation, scanstate->ss.ps.state->es_snapshot, @@ -299,8 +404,7 @@ tablesample_init(SampleScanState *scanstate) scanstate->use_bulkread, allow_sync, scanstate->use_pagemode, - ScanRelIsReadOnly(&scanstate->ss) ? - SO_HINT_REL_READ_ONLY : SO_NONE); + flags); } else { diff --git a/src/backend/executor/nodeTidscan.c b/src/backend/executor/nodeTidscan.c index b63329de330..ff516f9c4e8 100644 --- a/src/backend/executor/nodeTidscan.c +++ b/src/backend/executor/nodeTidscan.c @@ -25,7 +25,9 @@ #include "access/sysattr.h" #include "access/tableam.h" #include "catalog/pg_type.h" +#include "executor/execParallel.h" #include "executor/executor.h" +#include "executor/instrument.h" #include "executor/nodeTidscan.h" #include "lib/qunique.h" #include "miscadmin.h" @@ -146,9 +148,17 @@ TidListEval(TidScanState *tidstate) * the node might never get executed. */ if (tidstate->ss.ss_currentScanDesc == NULL) + { + uint32 flags = SO_NONE; + + if (tidstate->ss.ps.state->es_instrument & INSTRUMENT_BUFFERS) + flags |= SO_SCAN_INSTRUMENT_STRATEGY; + tidstate->ss.ss_currentScanDesc = table_beginscan_tid(tidstate->ss.ss_currentRelation, - tidstate->ss.ps.state->es_snapshot); + tidstate->ss.ps.state->es_snapshot, + flags); + } scan = tidstate->ss.ss_currentScanDesc; /* @@ -473,8 +483,106 @@ ExecReScanTidScan(TidScanState *node) void ExecEndTidScan(TidScanState *node) { - if (node->ss.ss_currentScanDesc) - table_endscan(node->ss.ss_currentScanDesc); + TableScanDesc scan = node->ss.ss_currentScanDesc; + + /* Collect scan statistics for this process into shared instrumentation */ + if (node->tss_sinstrument != NULL && IsParallelWorker()) + { + TidScanInstrumentation *si; + + Assert(ParallelWorkerNumber < node->tss_sinstrument->num_workers); + si = &node->tss_sinstrument->sinstrument[ParallelWorkerNumber]; + + if (scan && scan->rs_instrument) + CombineTableScanInstrumentation(&si->stats, scan->rs_instrument); + } + + if (scan) + table_endscan(scan); +} + +/* + * Compute the amount of space needed for shared TID scan instrumentation. + */ +void +ExecTidScanInstrumentEstimate(TidScanState *node, ParallelContext *pcxt) +{ + EState *estate = node->ss.ps.state; + Size size; + + if ((estate->es_instrument & INSTRUMENT_BUFFERS) == 0 || + pcxt->nworkers == 0) + return; + + size = add_size(offsetof(SharedTidScanInstrumentation, sinstrument), + mul_size(pcxt->nworkers, sizeof(TidScanInstrumentation))); + + shm_toc_estimate_chunk(&pcxt->estimator, size); + shm_toc_estimate_keys(&pcxt->estimator, 1); +} + +/* + * Set up shared TID scan instrumentation. + */ +void +ExecTidScanInstrumentInitDSM(TidScanState *node, ParallelContext *pcxt) +{ + EState *estate = node->ss.ps.state; + SharedTidScanInstrumentation *sinstrument; + Size size; + + if ((estate->es_instrument & INSTRUMENT_BUFFERS) == 0 || + pcxt->nworkers == 0) + return; + + size = add_size(offsetof(SharedTidScanInstrumentation, sinstrument), + mul_size(pcxt->nworkers, sizeof(TidScanInstrumentation))); + + sinstrument = shm_toc_allocate(pcxt->toc, size); + memset(sinstrument, 0, size); + sinstrument->num_workers = pcxt->nworkers; + + shm_toc_insert(pcxt->toc, node->ss.ps.plan->plan_node_id + + PARALLEL_KEY_SCAN_INSTRUMENT_OFFSET, + sinstrument); + + node->tss_sinstrument = sinstrument; +} + +/* + * Look up the shared TID scan instrumentation in a parallel worker. + */ +void +ExecTidScanInstrumentInitWorker(TidScanState *node, ParallelWorkerContext *pwcxt) +{ + EState *estate = node->ss.ps.state; + + if ((estate->es_instrument & INSTRUMENT_BUFFERS) == 0) + return; + + node->tss_sinstrument = shm_toc_lookup(pwcxt->toc, + node->ss.ps.plan->plan_node_id + + PARALLEL_KEY_SCAN_INSTRUMENT_OFFSET, + false); +} + +/* + * Transfer TID scan instrumentation from DSM to private memory. + */ +void +ExecTidScanRetrieveInstrumentation(TidScanState *node) +{ + SharedTidScanInstrumentation *sinstrument = node->tss_sinstrument; + Size size; + + if (sinstrument == NULL) + return; + + size = offsetof(SharedTidScanInstrumentation, sinstrument) + + sinstrument->num_workers * sizeof(TidScanInstrumentation); + + node->tss_sinstrument = palloc(size); + memcpy(node->tss_sinstrument, sinstrument, size); } /* ---------------------------------------------------------------- diff --git a/src/backend/utils/adt/tid.c b/src/backend/utils/adt/tid.c index f730b2f07c2..223a75866f6 100644 --- a/src/backend/utils/adt/tid.c +++ b/src/backend/utils/adt/tid.c @@ -355,7 +355,7 @@ currtid_internal(Relation rel, const ItemPointerData *tid) ItemPointerCopy(tid, result); snapshot = RegisterSnapshot(GetLatestSnapshot()); - scan = table_beginscan_tid(rel, snapshot); + scan = table_beginscan_tid(rel, snapshot, SO_NONE); table_tuple_get_latest_tid(scan, result); table_endscan(scan); UnregisterSnapshot(snapshot); diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index 9ad8dde300f..2a649431e13 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -1032,14 +1032,16 @@ table_beginscan_sampling(Relation rel, Snapshot snapshot, * table_beginscan_tid is an alternative entry point for setting up a * TableScanDesc for a Tid scan. As with bitmap scans, it's worth using * the same data structure although the behavior is rather different. + * + * flags is a bitmask of ScanOptions. No SO_INTERNAL_FLAGS are permitted. */ static inline TableScanDesc -table_beginscan_tid(Relation rel, Snapshot snapshot) +table_beginscan_tid(Relation rel, Snapshot snapshot, uint32 flags) { - uint32 flags = SO_TYPE_TIDSCAN; + uint32 internal_flags = SO_TYPE_TIDSCAN; return table_beginscan_common(rel, snapshot, 0, NULL, NULL, - flags, SO_NONE); + internal_flags, flags); } /* diff --git a/src/include/executor/instrument_node.h b/src/include/executor/instrument_node.h index 90216ee07c5..77abccfe3e1 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -352,6 +352,43 @@ typedef struct SharedSeqScanInstrumentation } SharedSeqScanInstrumentation; +/* --------------------- + * Instrumentation information for sample scans + * --------------------- + */ +typedef struct SampleScanInstrumentation +{ + TableScanInstrumentation stats; +} SampleScanInstrumentation; + +/* + * Shared memory container for per-worker information + */ +typedef struct SharedSampleScanInstrumentation +{ + int num_workers; + pg_attribute_counted_by(num_workers) SampleScanInstrumentation sinstrument[FLEXIBLE_ARRAY_MEMBER]; +} SharedSampleScanInstrumentation; + +/* --------------------- + * Instrumentation information for TID scans + * --------------------- + */ +typedef struct TidScanInstrumentation +{ + TableScanInstrumentation stats; +} TidScanInstrumentation; + +/* + * Shared memory container for per-worker information + */ +typedef struct SharedTidScanInstrumentation +{ + int num_workers; + pg_attribute_counted_by(num_workers) TidScanInstrumentation sinstrument[FLEXIBLE_ARRAY_MEMBER]; +} SharedTidScanInstrumentation; + + /* * Instrumentation information for TID range scans */ diff --git a/src/include/executor/nodeSamplescan.h b/src/include/executor/nodeSamplescan.h index d08d2be44e2..d2db06b6c2c 100644 --- a/src/include/executor/nodeSamplescan.h +++ b/src/include/executor/nodeSamplescan.h @@ -14,10 +14,20 @@ #ifndef NODESAMPLESCAN_H #define NODESAMPLESCAN_H +#include "access/parallel.h" #include "nodes/execnodes.h" extern SampleScanState *ExecInitSampleScan(SampleScan *node, EState *estate, int eflags); extern void ExecEndSampleScan(SampleScanState *node); extern void ExecReScanSampleScan(SampleScanState *node); +/* instrument support */ +extern void ExecSampleScanInstrumentEstimate(SampleScanState *node, + ParallelContext *pcxt); +extern void ExecSampleScanInstrumentInitDSM(SampleScanState *node, + ParallelContext *pcxt); +extern void ExecSampleScanInstrumentInitWorker(SampleScanState *node, + ParallelWorkerContext *pwcxt); +extern void ExecSampleScanRetrieveInstrumentation(SampleScanState *node); + #endif /* NODESAMPLESCAN_H */ diff --git a/src/include/executor/nodeTidscan.h b/src/include/executor/nodeTidscan.h index 65f8587986f..8729fdbd12e 100644 --- a/src/include/executor/nodeTidscan.h +++ b/src/include/executor/nodeTidscan.h @@ -14,10 +14,20 @@ #ifndef NODETIDSCAN_H #define NODETIDSCAN_H +#include "access/parallel.h" #include "nodes/execnodes.h" extern TidScanState *ExecInitTidScan(TidScan *node, EState *estate, int eflags); extern void ExecEndTidScan(TidScanState *node); extern void ExecReScanTidScan(TidScanState *node); +/* instrument support */ +extern void ExecTidScanInstrumentEstimate(TidScanState *node, + ParallelContext *pcxt); +extern void ExecTidScanInstrumentInitDSM(TidScanState *node, + ParallelContext *pcxt); +extern void ExecTidScanInstrumentInitWorker(TidScanState *node, + ParallelWorkerContext *pwcxt); +extern void ExecTidScanRetrieveInstrumentation(TidScanState *node); + #endif /* NODETIDSCAN_H */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 8087a986204..0b4b32bff9b 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1673,6 +1673,7 @@ typedef struct SampleScanState int64 donetuples; /* number of tuples already returned */ bool haveblock; /* has a block for sampling been determined */ bool done; /* exhausted all tuples? */ + struct SharedSampleScanInstrumentation *sinstrument; } SampleScanState; /* @@ -1875,6 +1876,7 @@ typedef struct TidScanState int tss_NumTids; int tss_TidPtr; ItemPointerData *tss_TidList; + struct SharedTidScanInstrumentation *tss_sinstrument; } TidScanState; /* ---------------- diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index b964e5ee907..c7ce7aaacde 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2767,6 +2767,7 @@ SYNCHRONIZATION_BARRIER SYSTEM_INFO SampleScan SampleScanGetSampleSize_function +SampleScanInstrumentation SampleScanState SavedTransactionCharacteristics ScalarArrayOpExpr @@ -2867,8 +2868,10 @@ SharedMemoizeInfo SharedRecordTableEntry SharedRecordTableKey SharedRecordTypmodRegistry +SharedSampleScanInstrumentation SharedSeqScanInstrumentation SharedSortInfo +SharedTidScanInstrumentation SharedTidRangeScanInstrumentation SharedTuplestore SharedTuplestoreAccessor @@ -3182,6 +3185,7 @@ TidRangeScan TidRangeScanInstrumentation TidRangeScanState TidScan +TidScanInstrumentation TidScanState TidStore TidStoreIter -- 2.34.1
>From 9ab870314726014344b06cbf48a898d501aa5e4c Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 06:49:22 +0000 Subject: [PATCH v3 7/8] Report buffer access strategies for table scans in EXPLAIN When ANALYZE and BUFFERS are enabled, report every buffer access strategy selected by sequential, sample, bitmap heap, TID, and TID range scans. Read the strategies from leader and per-worker table scan instrumentation and report them only for processes that execute the scan. If rescans select different strategies, include each one in the reported list. Add documentation and TAP coverage for serial and parallel execution, rescans, never-executed scans, and omission when BUFFERS is disabled. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- doc/src/sgml/ref/explain.sgml | 12 + src/backend/commands/explain.c | 128 ++++++++++ src/test/modules/test_misc/meson.build | 1 + .../t/016_explain_buffer_strategy.pl | 237 ++++++++++++++++++ src/test/regress/expected/explain.out | 234 +++++++++-------- src/test/regress/sql/explain.sql | 2 + 6 files changed, 503 insertions(+), 111 deletions(-) 3.5% doc/src/sgml/ref/ 15.8% src/backend/commands/ 34.9% src/test/modules/test_misc/t/ 44.8% src/test/regress/expected/ diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index f38d31e106a..3841f171bb3 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -200,6 +200,18 @@ ROLLBACK; number of blocks <emphasis>written</emphasis> indicates the number of previously-dirtied blocks evicted from cache by this backend during query processing. + When both <literal>ANALYZE</literal> and <literal>BUFFERS</literal> are + enabled, executed <literal>Seq Scan</literal>, + <literal>Sample Scan</literal>, <literal>Bitmap Heap Scan</literal>, + <literal>TID Scan</literal>, and <literal>TID Range Scan</literal> plan + nodes report the <glossterm + linkend="glossary-buffer-access-strategy">buffer access + strategies</glossterm> used by the table scan when the table access + method provides them. The reported list includes every strategy + selected while executing the scan, including across rescans. + Only strategies used by scan nodes are reported; write-side strategies + such as <literal>bulkwrite</literal> are not shown, even when a command + uses them internally. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text format, only non-zero values are printed. Buffers information is 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 @@ -140,6 +140,7 @@ static void show_hashagg_info(AggState *aggstate, ExplainState *es); static void show_indexscan_info(PlanState *planstate, ExplainState *es); static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es); +static void show_scan_buffer_access_strategies(ScanState *planstate, ExplainState *es); static void show_scan_io_usage(ScanState *planstate, ExplainState *es); static void show_instrumentation_count(const char *qlabel, int which, @@ -2010,6 +2011,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_tidbitmap_info((BitmapHeapScanState *) planstate, es); + show_scan_buffer_access_strategies((ScanState *) planstate, es); show_scan_io_usage((ScanState *) planstate, es); break; case T_SampleScan: @@ -2029,6 +2031,7 @@ ExplainNode(PlanState *planstate, List *ancestors, planstate, es); if (IsA(plan, CteScan)) show_ctescan_info(castNode(CteScanState, planstate), es); + show_scan_buffer_access_strategies((ScanState *) planstate, es); show_scan_io_usage((ScanState *) planstate, es); break; case T_Gather: @@ -2129,6 +2132,7 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); + show_scan_buffer_access_strategies((ScanState *) planstate, es); } break; case T_TidRangeScan: @@ -2146,6 +2150,7 @@ ExplainNode(PlanState *planstate, List *ancestors, if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); + show_scan_buffer_access_strategies((ScanState *) planstate, es); show_scan_io_usage((ScanState *) planstate, es); } break; @@ -4027,6 +4032,49 @@ show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es) } } +/* + * Return the name of a buffer access strategy for EXPLAIN. + */ +static const char * +buffer_access_strategy_name(BufferAccessStrategyType strategy) +{ + switch (strategy) + { + case BAS_NORMAL: + return "normal"; + case BAS_BULKREAD: + return "bulkread"; + case BAS_BULKWRITE: + return "bulkwrite"; + case BAS_VACUUM: + return "vacuum"; + } + + elog(ERROR, "unrecognized buffer access strategy type: %d", strategy); + pg_unreachable(); +} + +/* + * Print all buffer access strategies observed while executing a scan. + */ +static void +print_buffer_access_strategies(ExplainState *es, uint32 strategy_mask) +{ + List *strategies = NIL; + + for (int strategy = BAS_NORMAL; strategy <= BAS_VACUUM; strategy++) + { + if (strategy_mask & BufferAccessStrategyBit(strategy)) + strategies = lappend(strategies, + unconstify(char *, + buffer_access_strategy_name(strategy))); + } + + Assert(strategies != NIL); + ExplainPropertyList("Buffer Access Strategies", strategies, es); + list_free(strategies); +} + /* * Print I/O stats - prefetching and I/O performed * @@ -4111,6 +4159,15 @@ get_table_scan_instrumentation(ScanState *planstate, int worker) SharedBitmapHeapInstrumentation *sinstrument = ((BitmapHeapScanState *) planstate)->sinstrument; + if (sinstrument && worker < sinstrument->num_workers) + return &sinstrument->sinstrument[worker].stats; + break; + } + case T_SampleScan: + { + SharedSampleScanInstrumentation *sinstrument = + ((SampleScanState *) planstate)->sinstrument; + if (sinstrument && worker < sinstrument->num_workers) return &sinstrument->sinstrument[worker].stats; break; @@ -4120,6 +4177,15 @@ get_table_scan_instrumentation(ScanState *planstate, int worker) SharedSeqScanInstrumentation *sinstrument = ((SeqScanState *) planstate)->sinstrument; + if (sinstrument && worker < sinstrument->num_workers) + return &sinstrument->sinstrument[worker].stats; + break; + } + case T_TidScan: + { + SharedTidScanInstrumentation *sinstrument = + ((TidScanState *) planstate)->tss_sinstrument; + if (sinstrument && worker < sinstrument->num_workers) return &sinstrument->sinstrument[worker].stats; break; @@ -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; + + /* Report the buffer access strategy only for these scan types. */ + switch (nodeTag(planstate->ps.plan)) + { + case T_BitmapHeapScan: + case T_SampleScan: + case T_SeqScan: + case T_TidScan: + case T_TidRangeScan: + break; + default: + return; + } + + instrument = get_table_scan_instrumentation(planstate, -1); + + if (worker_instrument) + { + /* + * After parallel cleanup, the node's loop count includes worker + * loops. Remove them to determine whether the leader executed the + * scan. + */ + for (int i = 0; i < worker_instrument->num_workers; ++i) + { + double worker_nloops = worker_instrument->instrument[i].nloops; + TableScanInstrumentation *winstrument = + get_table_scan_instrumentation(planstate, i); + + leader_nloops -= worker_nloops; + + if (!winstrument || winstrument->strategy_mask == 0 || + worker_nloops <= 0) + continue; + + if (es->workers_state) + ExplainOpenWorker(i, es); + + print_buffer_access_strategies(es, winstrument->strategy_mask); + + if (es->workers_state) + ExplainCloseWorker(i, es); + } + } + + if (instrument && instrument->strategy_mask != 0 && leader_nloops > 0) + print_buffer_access_strategies(es, instrument->strategy_mask); +} + /* * Show information about prefetch and I/O in a scan node. */ diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build index 5d81f5b13be..e71754ebbc3 100644 --- a/src/test/modules/test_misc/meson.build +++ b/src/test/modules/test_misc/meson.build @@ -24,6 +24,7 @@ tests += { 't/013_temp_obj_multisession.pl', 't/014_log_statement_max_length.pl', 't/015_temp_schema_exit_deferrable.pl', + 't/016_explain_buffer_strategy.pl', ], # The injection points are cluster-wide, so disable installcheck 'runningcheck': false, diff --git a/src/test/modules/test_misc/t/016_explain_buffer_strategy.pl b/src/test/modules/test_misc/t/016_explain_buffer_strategy.pl new file mode 100644 index 00000000000..3d8f3aa89d3 --- /dev/null +++ b/src/test/modules/test_misc/t/016_explain_buffer_strategy.pl @@ -0,0 +1,237 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test EXPLAIN reporting of buffer access strategies for table scans, +# including rescans, parallel execution, and cases where no strategy is shown. + +use strict; +use warnings FATAL => 'all'; + +use JSON::PP qw(decode_json); +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node = PostgreSQL::Test::Cluster->new('main'); +$node->init; +$node->append_conf( + 'postgresql.conf', qq{ +shared_buffers = '1MB' +max_parallel_workers_per_gather = 0 +}); +$node->start; + +$node->safe_psql( + 'postgres', qq{ +CREATE TABLE test_strategy AS + SELECT g, repeat('x', 1000) AS payload + FROM generate_series(1, 4000) AS g; +ALTER TABLE test_strategy SET (parallel_workers = 2); +CREATE INDEX test_strategy_g_idx ON test_strategy (g); +CREATE TABLE test_normal AS SELECT 1 AS g; +}); + +my $seqscan = explain_json('SELECT * FROM test_strategy'); +is($seqscan->{'Node Type'}, 'Seq Scan', 'sequential scan plan'); +check_strategy($seqscan, 'bulkread', 'sequential scan'); + +my $normal_scan = explain_json('SELECT * FROM test_normal'); +is($normal_scan->{'Node Type'}, 'Seq Scan', 'small sequential scan plan'); +check_strategy($normal_scan, 'normal', 'small sequential scan'); + +my $never_executed_plan = decode_json( + $node->safe_psql( + 'postgres', qq{ +SET plan_cache_mode = force_generic_plan; +PREPARE never_execute(bool) AS SELECT * FROM test_normal WHERE \$1; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + EXECUTE never_execute(false); +}))->[0]->{'Plan'}; +my $never_executed_scan = find_plan_node($never_executed_plan, 'Seq Scan'); +ok(defined $never_executed_scan, + 'never-executed plan contains a sequential scan'); +is($never_executed_scan->{'Actual Loops'}, + 0, 'sequential scan was not executed'); +ok( !exists $never_executed_scan->{'Buffer Access Strategies'}, + 'strategy is not shown for a never-executed scan'); + +# Sample scans use the buffer access strategy without a read stream. +my $samplescan = + explain_json('SELECT * FROM test_strategy TABLESAMPLE SYSTEM (100)'); +is($samplescan->{'Node Type'}, 'Sample Scan', 'sample scan plan'); +check_strategy($samplescan, 'bulkread', 'sample scan'); + +my $rescan_plan = explain_json( + q{ +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 test_strategy TABLESAMPLE SYSTEM (o.p) REPEATABLE (0) +) s ON true +}); +my $rescan = find_plan_node($rescan_plan, 'Sample Scan'); +ok(defined $rescan, 'parameterized sample scan plan'); +is($rescan->{'Actual Loops'}, 2, 'parameterized sample scan was rescanned'); +is_deeply( + $rescan->{'Buffer Access Strategies'}, + [ 'normal', 'bulkread' ], + 'parameterized sample scan reports every strategy used'); + +my $bitmap_plan = decode_json( + $node->safe_psql( + 'postgres', qq{ +SET enable_seqscan = off; +SET enable_indexscan = off; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT * FROM test_strategy WHERE g BETWEEN 1 AND 1000; +}))->[0]->{'Plan'}; +my $bitmap_scan = find_plan_node($bitmap_plan, 'Bitmap Heap Scan'); +ok(defined $bitmap_scan, 'bitmap heap scan plan'); +check_strategy($bitmap_scan, 'normal', 'bitmap heap scan'); + +my $tid_plan = + explain_json("SELECT * FROM test_strategy WHERE ctid = '(0,1)'"); +my $tid_scan = find_plan_node($tid_plan, 'Tid Scan'); +ok(defined $tid_scan, 'TID scan plan'); +check_strategy($tid_scan, 'normal', 'TID scan'); + +my $tidrange_plan = + explain_json( + "SELECT * FROM test_strategy WHERE ctid >= '(0,1)' AND ctid < '(1,1)'"); +my $tidrange_scan = find_plan_node($tidrange_plan, 'Tid Range Scan'); +ok(defined $tidrange_scan, 'TID range scan plan'); +check_strategy($tidrange_scan, 'normal', 'TID range scan'); + +my $worker_samplescan_plan = decode_json( + $node->safe_psql( + 'postgres', qq{ +SET max_parallel_workers_per_gather = 1; +SET debug_parallel_query = on; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT * FROM test_strategy TABLESAMPLE SYSTEM (100); +}))->[0]->{'Plan'}; +check_worker_strategy($worker_samplescan_plan, 'Sample Scan', 'bulkread', + 'worker-only sample scan'); + +my $worker_tid_plan = decode_json( + $node->safe_psql( + 'postgres', qq{ +SET max_parallel_workers_per_gather = 1; +SET debug_parallel_query = on; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT * FROM test_strategy WHERE ctid = '(0,1)'; +}))->[0]->{'Plan'}; +check_worker_strategy($worker_tid_plan, 'Tid Scan', 'normal', + 'worker-only TID scan'); + +my $without_buffers = decode_json( + $node->safe_psql( + 'postgres', + 'EXPLAIN (ANALYZE, BUFFERS OFF, IO, FORMAT JSON) ' + . 'SELECT * FROM test_strategy'))->[0]->{'Plan'}; +ok( !exists $without_buffers->{'Buffer Access Strategies'}, + 'strategy is not shown when BUFFERS is disabled'); +ok( exists $without_buffers->{'Average Prefetch Distance'}, + 'read stream statistics are shown when only IO is enabled'); + +my $text = $node->safe_psql('postgres', + 'EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF) ' + . 'SELECT * FROM test_strategy'); +like( + $text, + qr/Buffer Access Strategies: bulkread/, + 'text output reports the strategy'); + +my $parallel_plan = decode_json( + $node->safe_psql( + 'postgres', qq{ +SET max_parallel_workers_per_gather = 2; +SET min_parallel_table_scan_size = 0; +SET parallel_setup_cost = 0; +SET parallel_tuple_cost = 0; +SET parallel_leader_participation = off; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT count(*) FROM test_strategy; +}))->[0]->{'Plan'}; +my $gather = find_plan_node($parallel_plan, 'Gather'); +my $parallel_scan = find_plan_node($parallel_plan, 'Seq Scan'); + +ok(defined $gather, 'parallel plan contains a Gather'); +cmp_ok($gather->{'Workers Launched'}, '>', 0, 'parallel workers launched'); +ok(defined $parallel_scan, 'parallel plan contains a sequential scan'); +ok( !exists $parallel_scan->{'Buffer Access Strategies'}, + 'strategy is not shown for a non-participating leader'); +my $workers = $parallel_scan->{'Workers'}; +ok(ref $workers eq 'ARRAY' && @$workers > 0, + 'parallel scan reports worker strategy information'); + +foreach my $worker (@$workers) +{ + is_deeply($worker->{'Buffer Access Strategies'}, + ['bulkread'], 'parallel worker reports the strategy'); +} + +$node->stop; + +done_testing(); + +# Run EXPLAIN for the query and return its top-level plan. +sub explain_json +{ + my ($query) = @_; + my $output = $node->safe_psql('postgres', + "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) $query"); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Check that a plan reports only the expected buffer access strategy. +sub check_strategy +{ + my ($plan, $strategy, $description) = @_; + + is_deeply($plan->{'Buffer Access Strategies'}, + [$strategy], "$description uses the $strategy strategy"); +} + +# Check that a worker-only scan reports only the expected strategy. +sub check_worker_strategy +{ + my ($plan, $type, $strategy, $description) = @_; + my $scan = find_plan_node($plan, $type); + + ok(defined $scan, "$description plan contains a $type"); + ok( !exists $scan->{'Buffer Access Strategies'}, + "$description does not report a leader strategy"); + + my $workers = $scan->{'Workers'}; + + ok( ref $workers eq 'ARRAY' && @$workers > 0, + "$description reports worker strategy information"); + foreach my $worker (@$workers) + { + is_deeply($worker->{'Buffer Access Strategies'}, + [$strategy], "$description worker uses the $strategy strategy"); + } +} + +# Return the first plan node of the requested type, or undef if none exists. +sub find_plan_node +{ + my ($plan, $type) = @_; + + return $plan if $plan->{'Node Type'} eq $type; + + foreach my $child (@{ $plan->{'Plans'} // [] }) + { + my $result = find_plan_node($child, $type); + + return $result if defined $result; + } + + return; +} diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index 74a4d87801e..9efbc8b1a0b 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -89,9 +89,10 @@ select explain_filter('explain (analyze, buffers, format text) select * from int explain_filter ------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + Buffer Access Strategies: normal Planning Time: N.N ms Execution Time: N.N ms -(3 rows) +(4 rows) select explain_filter('explain (buffers, format text) select * from int8_tbl i8'); explain_filter @@ -119,6 +120,9 @@ explain_filter <Actual-Rows>N.N</Actual-Rows> <Actual-Loops>N</Actual-Loops> <Disabled>false</Disabled> + <Buffer-Access-Strategies> + <Item>normal</Item> + </Buffer-Access-Strategies> <Average-Prefetch-Distance>N.N</Average-Prefetch-Distance> <Max-Prefetch-Distance>N</Max-Prefetch-Distance> <Prefetch-Capacity>N</Prefetch-Capacity> @@ -173,6 +177,8 @@ explain_filter Actual Rows: N.N Actual Loops: N Disabled: false + Buffer Access Strategies: + - "normal" Average Prefetch Distance: N.N Max Prefetch Distance: N Prefetch Capacity: N @@ -301,65 +307,66 @@ select explain_filter('explain verbose select sum(unique1) over w1, sum(unique2) -- but always set in JSON format, so check them only in this case. set track_io_timing = on; select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8'); - explain_filter -------------------------------------- - [ + - { + - "Plan": { + - "Node Type": "Seq Scan", + - "Parallel Aware": false, + - "Async Capable": false, + - "Relation Name": "int8_tbl", + - "Alias": "i8", + - "Startup Cost": N.N, + - "Total Cost": N.N, + - "Plan Rows": N, + - "Plan Width": N, + - "Actual Startup Time": N.N, + - "Actual Total Time": N.N, + - "Actual Rows": N.N, + - "Actual Loops": N, + - "Disabled": false, + - "Shared Hit Blocks": N, + - "Shared Read Blocks": N, + - "Shared Dirtied Blocks": N, + - "Shared Written Blocks": N, + - "Local Hit Blocks": N, + - "Local Read Blocks": N, + - "Local Dirtied Blocks": N, + - "Local Written Blocks": N, + - "Temp Read Blocks": N, + - "Temp Written Blocks": N, + - "Shared I/O Read Time": N.N, + - "Shared I/O Write Time": N.N,+ - "Local I/O Read Time": N.N, + - "Local I/O Write Time": N.N, + - "Temp I/O Read Time": N.N, + - "Temp I/O Write Time": N.N + - }, + - "Planning": { + - "Shared Hit Blocks": N, + - "Shared Read Blocks": N, + - "Shared Dirtied Blocks": N, + - "Shared Written Blocks": N, + - "Local Hit Blocks": N, + - "Local Read Blocks": N, + - "Local Dirtied Blocks": N, + - "Local Written Blocks": N, + - "Temp Read Blocks": N, + - "Temp Written Blocks": N, + - "Shared I/O Read Time": N.N, + - "Shared I/O Write Time": N.N,+ - "Local I/O Read Time": N.N, + - "Local I/O Write Time": N.N, + - "Temp I/O Read Time": N.N, + - "Temp I/O Write Time": N.N + - }, + - "Planning Time": N.N, + - "Triggers": [ + - ], + - "Execution Time": N.N + - } + + explain_filter +----------------------------------------------- + [ + + { + + "Plan": { + + "Node Type": "Seq Scan", + + "Parallel Aware": false, + + "Async Capable": false, + + "Relation Name": "int8_tbl", + + "Alias": "i8", + + "Startup Cost": N.N, + + "Total Cost": N.N, + + "Plan Rows": N, + + "Plan Width": N, + + "Actual Startup Time": N.N, + + "Actual Total Time": N.N, + + "Actual Rows": N.N, + + "Actual Loops": N, + + "Disabled": false, + + "Buffer Access Strategies": ["normal"],+ + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N, + + "Shared I/O Read Time": N.N, + + "Shared I/O Write Time": N.N, + + "Local I/O Read Time": N.N, + + "Local I/O Write Time": N.N, + + "Temp I/O Read Time": N.N, + + "Temp I/O Write Time": N.N + + }, + + "Planning": { + + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N, + + "Shared I/O Read Time": N.N, + + "Shared I/O Write Time": N.N, + + "Local I/O Read Time": N.N, + + "Local I/O Write Time": N.N, + + "Temp I/O Read Time": N.N, + + "Temp I/O Write Time": N.N + + }, + + "Planning Time": N.N, + + "Triggers": [ + + ], + + "Execution Time": N.N + + } + ] (1 row) @@ -436,55 +443,56 @@ select explain_filter('explain (memory, summary, format yaml) select * from int8 (1 row) select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); - explain_filter ------------------------------------- - [ + - { + - "Plan": { + - "Node Type": "Seq Scan", + - "Parallel Aware": false, + - "Async Capable": false, + - "Relation Name": "int8_tbl",+ - "Alias": "i8", + - "Startup Cost": N.N, + - "Total Cost": N.N, + - "Plan Rows": N, + - "Plan Width": N, + - "Actual Startup Time": N.N, + - "Actual Total Time": N.N, + - "Actual Rows": N.N, + - "Actual Loops": N, + - "Disabled": false, + - "Shared Hit Blocks": N, + - "Shared Read Blocks": N, + - "Shared Dirtied Blocks": N, + - "Shared Written Blocks": N, + - "Local Hit Blocks": N, + - "Local Read Blocks": N, + - "Local Dirtied Blocks": N, + - "Local Written Blocks": N, + - "Temp Read Blocks": N, + - "Temp Written Blocks": N + - }, + - "Planning": { + - "Shared Hit Blocks": N, + - "Shared Read Blocks": N, + - "Shared Dirtied Blocks": N, + - "Shared Written Blocks": N, + - "Local Hit Blocks": N, + - "Local Read Blocks": N, + - "Local Dirtied Blocks": N, + - "Local Written Blocks": N, + - "Temp Read Blocks": N, + - "Temp Written Blocks": N, + - "Memory Used": N, + - "Memory Allocated": N + - }, + - "Planning Time": N.N, + - "Triggers": [ + - ], + - "Execution Time": N.N + - } + + explain_filter +----------------------------------------------- + [ + + { + + "Plan": { + + "Node Type": "Seq Scan", + + "Parallel Aware": false, + + "Async Capable": false, + + "Relation Name": "int8_tbl", + + "Alias": "i8", + + "Startup Cost": N.N, + + "Total Cost": N.N, + + "Plan Rows": N, + + "Plan Width": N, + + "Actual Startup Time": N.N, + + "Actual Total Time": N.N, + + "Actual Rows": N.N, + + "Actual Loops": N, + + "Disabled": false, + + "Buffer Access Strategies": ["normal"],+ + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N + + }, + + "Planning": { + + "Shared Hit Blocks": N, + + "Shared Read Blocks": N, + + "Shared Dirtied Blocks": N, + + "Shared Written Blocks": N, + + "Local Hit Blocks": N, + + "Local Read Blocks": N, + + "Local Dirtied Blocks": N, + + "Local Written Blocks": N, + + "Temp Read Blocks": N, + + "Temp Written Blocks": N, + + "Memory Used": N, + + "Memory Allocated": N + + }, + + "Planning Time": N.N, + + "Triggers": [ + + ], + + "Execution Time": N.N + + } + ] (1 row) @@ -542,6 +550,8 @@ select jsonb_pretty( select * from tenk1 order by tenthous') -- remove "Workers" node of the Seq Scan plan node #- '{0,Plan,Plans,0,Plans,0,Workers}' + -- remove its buffer access strategies, as they depend on shared_buffers + #- '{0,Plan,Plans,0,Plans,0,Buffer Access Strategies}' -- remove "Workers" node of the Sort plan node #- '{0,Plan,Plans,0,Workers}' -- Also remove its sort-type fields, as those aren't 100% stable @@ -767,19 +777,21 @@ select explain_filter('explain (analyze,serialize text,buffers,timing off) selec explain_filter ----------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual rows=N.N loops=N) + Buffer Access Strategies: normal Planning Time: N.N ms Serialization: output=NkB format=text Execution Time: N.N ms -(4 rows) +(5 rows) select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) + Buffer Access Strategies: normal Planning Time: N.N ms Serialization: time=N.N ms output=NkB format=binary Execution Time: N.N ms -(4 rows) +(5 rows) -- this tests an edge case where we have no data to return select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index 2f163c64bf6..e2b9e80c678 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -151,6 +151,8 @@ select jsonb_pretty( select * from tenk1 order by tenthous') -- remove "Workers" node of the Seq Scan plan node #- '{0,Plan,Plans,0,Plans,0,Workers}' + -- remove its buffer access strategies, as they depend on shared_buffers + #- '{0,Plan,Plans,0,Plans,0,Buffer Access Strategies}' -- remove "Workers" node of the Sort plan node #- '{0,Plan,Plans,0,Workers}' -- Also remove its sort-type fields, as those aren't 100% stable -- 2.34.1
>From b5c3ec87d3ae0421f3617b9779327a052b35b916 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Sat, 24 Oct 2026 09:38:46 +0000 Subject: [PATCH v3 8/8] Report buffer access strategies for index scans in EXPLAIN When ANALYZE and BUFFERS are enabled, report every buffer access strategy selected by the index access method for index, index-only, and bitmap index scans. Extend index scan instrumentation with an optional strategy mask and request it only when buffer instrumentation is enabled. Have each in-core index access method record its selected strategy in the scan instrumentation. The standard access methods report normal, while Bloom reports bulkread. Preserve strategies across rescans and propagate them through parallel worker instrumentation. Report them only for the leader and workers that execute the scan. An empty mask allows another index access method to leave the information unavailable. Add documentation and TAP coverage for normal and bulkread strategies, parallel workers, never-executed scans, scans that exit before searching the index, and omission when BUFFERS is disabled. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- contrib/bloom/blscan.c | 1 + contrib/bloom/meson.build | 1 + contrib/bloom/t/002_explain.pl | 259 +++++++++++++++++++++ doc/src/sgml/ref/explain.sgml | 8 +- src/backend/access/brin/brin.c | 1 + src/backend/access/gin/ginget.c | 3 + src/backend/access/gist/gistget.c | 2 + src/backend/access/hash/hashsearch.c | 1 + src/backend/access/nbtree/nbtsearch.c | 2 + src/backend/access/spgist/spgscan.c | 6 + src/backend/commands/explain.c | 51 +++- src/backend/executor/nodeBitmapIndexscan.c | 6 +- src/backend/executor/nodeIndexonlyscan.c | 7 +- src/backend/executor/nodeIndexscan.c | 6 +- src/include/executor/instrument_node.h | 37 +++ 15 files changed, 375 insertions(+), 16 deletions(-) 60.3% contrib/bloom/t/ 4.0% doc/src/sgml/ref/ 4.8% src/backend/access/ 15.9% src/backend/commands/ 6.1% src/backend/executor/ 7.8% src/include/executor/ diff --git a/contrib/bloom/blscan.c b/contrib/bloom/blscan.c index 1a0e42021ec..ecb519d704a 100644 --- a/contrib/bloom/blscan.c +++ b/contrib/bloom/blscan.c @@ -117,6 +117,7 @@ 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); npages = RelationGetNumberOfBlocks(scan->indexRelation); pgstat_count_index_scan(scan->indexRelation); diff --git a/contrib/bloom/meson.build b/contrib/bloom/meson.build index fa4f4ea796b..3e6a5667ef1 100644 --- a/contrib/bloom/meson.build +++ b/contrib/bloom/meson.build @@ -40,6 +40,7 @@ tests += { 'tap': { 'tests': [ 't/001_wal.pl', + 't/002_explain.pl', ], }, } diff --git a/contrib/bloom/t/002_explain.pl b/contrib/bloom/t/002_explain.pl new file mode 100644 index 00000000000..1eb959d4bac --- /dev/null +++ b/contrib/bloom/t/002_explain.pl @@ -0,0 +1,259 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test EXPLAIN reporting of buffer access strategies for index scans, +# including different access methods, early exits, and parallel execution. + +use strict; +use warnings FATAL => 'all'; + +use JSON::PP qw(decode_json); +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node = PostgreSQL::Test::Cluster->new('main'); +$node->init; + +# Keep the test table below the bulk-read threshold +$node->append_conf('postgresql.conf', 'shared_buffers = 1MB'); +$node->start; + +$node->safe_psql( + 'postgres', qq{ +CREATE EXTENSION bloom; +CREATE TABLE tst AS SELECT i % 10 AS i FROM generate_series(1, 2000) AS i; +CREATE INDEX bloomidx ON tst USING bloom (i); +CREATE TABLE btree_tst AS + SELECT i, i AS payload FROM generate_series(1, 2000) AS i; +CREATE INDEX btreeidx ON btree_tst (i); +CREATE TABLE gin_tst AS + SELECT ARRAY[i] AS a FROM generate_series(1, 2000) AS i; +CREATE INDEX ginidx ON gin_tst USING gin (a); +}); + +my $btree_plan = explain_btree_json(); +my $btree_scan = find_plan_node($btree_plan, 'Index Scan'); + +ok(defined $btree_scan, 'plan contains a B-tree index scan'); +is_deeply($btree_scan->{'Buffer Access Strategies'}, + ['normal'], 'B-tree index scan uses the normal strategy'); + +my $btree_index_only_plan = explain_btree_index_only_json(); +my $btree_index_only_scan = + find_plan_node($btree_index_only_plan, 'Index Only Scan'); + +ok(defined $btree_index_only_scan, 'plan contains a B-tree index-only scan'); +is_deeply($btree_index_only_scan->{'Buffer Access Strategies'}, + ['normal'], 'B-tree index-only scan uses the normal strategy'); + +my $btree_null_plan = explain_btree_null_json(); +my $btree_null_scan = find_plan_node($btree_null_plan, 'Index Scan'); + +ok(defined $btree_null_scan, + 'plan contains a B-tree index scan for a NULL parameter'); +is($btree_null_scan->{'Index Searches'}, + 0, 'B-tree exits before searching the index for a NULL parameter'); +ok( !exists $btree_null_scan->{'Buffer Access Strategies'}, + 'strategy is not shown when B-tree does not search the index'); + +my $never_executed_plan = explain_never_executed_gin_json(); +my $never_executed_scan = + find_never_executed_plan_node($never_executed_plan, 'Bitmap Index Scan'); + +ok(defined $never_executed_scan, + 'plan contains a never-executed GIN index scan'); +ok( !exists $never_executed_scan->{'Buffer Access Strategies'}, + 'strategy is not shown for a never-executed index scan'); + +my $plan = explain_json('BUFFERS'); +my $heap_scan = find_plan_node($plan, 'Bitmap Heap Scan'); +my $index_scan = find_plan_node($plan, 'Bitmap Index Scan'); + +ok(defined $heap_scan, 'plan contains a bitmap heap scan'); +is_deeply($heap_scan->{'Buffer Access Strategies'}, + ['normal'], 'bitmap heap scan uses the normal strategy'); +ok(defined $index_scan, 'plan contains a bitmap index scan'); +is_deeply($index_scan->{'Buffer Access Strategies'}, + ['bulkread'], 'bloom index scan uses the bulkread strategy'); + +my $null_plan = explain_bloom_null_json(); +my $null_index_scan = find_plan_node($null_plan, 'Bitmap Index Scan'); + +ok(defined $null_index_scan, + 'plan contains a Bloom bitmap index scan for a NULL parameter'); +is($null_index_scan->{'Index Searches'}, + 0, 'Bloom exits before searching the index for a NULL parameter'); +ok( !exists $null_index_scan->{'Buffer Access Strategies'}, + 'strategy is not shown when Bloom does not search the index'); + +my $parallel_plan = explain_parallel_json(); +my $gather = find_plan_node($parallel_plan, 'Gather'); +my $parallel_index_scan = find_plan_node($parallel_plan, 'Bitmap Index Scan'); + +ok(defined $gather, 'parallel plan contains a Gather'); +cmp_ok(defined $gather ? $gather->{'Workers Launched'} : 0, + '>', 0, 'parallel worker launched'); +ok(defined $parallel_index_scan, + 'parallel plan contains a bitmap index scan'); +ok( !exists $parallel_index_scan->{'Buffer Access Strategies'}, + 'strategy is not shown for the non-participating leader'); +my $workers = $parallel_index_scan->{'Workers'}; +ok(ref $workers eq 'ARRAY' && @$workers > 0, + 'parallel bitmap index scan reports worker information'); + +foreach my $worker (@$workers) +{ + is_deeply($worker->{'Buffer Access Strategies'}, + ['bulkread'], 'parallel worker uses the bulkread strategy'); +} + +my $without_buffers = explain_json('BUFFERS OFF'); +$index_scan = find_plan_node($without_buffers, 'Bitmap Index Scan'); + +ok( defined $index_scan && !exists $index_scan->{'Buffer Access Strategies'}, + 'strategy is not shown when BUFFERS is disabled'); + +$node->stop; + +done_testing(); + +# Run EXPLAIN for a B-tree index scan and return the top-level plan. +sub explain_btree_json +{ + my $output = $node->safe_psql( + 'postgres', qq{ +SET enable_seqscan = off; +SET enable_bitmapscan = off; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT * FROM btree_tst WHERE i = 7; +}); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Run EXPLAIN for a B-tree index-only scan and return the top-level plan. +sub explain_btree_index_only_json +{ + my $output = $node->safe_psql( + 'postgres', qq{ +SET enable_seqscan = off; +SET enable_bitmapscan = off; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT i FROM btree_tst WHERE i = 7; +}); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Run EXPLAIN for a B-tree scan that exits before reading the index. +sub explain_btree_null_json +{ + my $output = $node->safe_psql( + 'postgres', qq{ +SET enable_seqscan = off; +SET enable_bitmapscan = off; +SET plan_cache_mode = force_generic_plan; +PREPARE btree_null_query(int) AS + SELECT * FROM btree_tst WHERE i = \$1; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + EXECUTE btree_null_query(NULL); +}); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Run EXPLAIN with a never-executed gin index scan. +sub explain_never_executed_gin_json +{ + my $output = $node->safe_psql( + 'postgres', qq{ +SET enable_seqscan = off; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT g.* + FROM btree_tst b + CROSS JOIN LATERAL + (SELECT * FROM gin_tst g WHERE g.a @> ARRAY[b.i] OFFSET 0) g + WHERE b.i = -1; +}); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Run EXPLAIN and return the top-level plan. +sub explain_json +{ + my ($buffers) = @_; + my $output = $node->safe_psql( + 'postgres', qq{ +SET enable_seqscan = off; +EXPLAIN (ANALYZE, $buffers, FORMAT JSON) + SELECT * FROM tst WHERE i = 7; +}); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Run EXPLAIN for a Bloom scan that exits before reading the index. +sub explain_bloom_null_json +{ + my $output = $node->safe_psql( + 'postgres', qq{ +SET enable_seqscan = off; +SET plan_cache_mode = force_generic_plan; +PREPARE bloom_null_query(int) AS SELECT * FROM tst WHERE i = \$1; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + EXECUTE bloom_null_query(NULL); +}); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Run EXPLAIN in a parallel worker and return the top-level plan. +sub explain_parallel_json +{ + my $output = $node->safe_psql( + 'postgres', qq{ +SET max_parallel_workers_per_gather = 1; +SET debug_parallel_query = on; +SET enable_seqscan = off; +EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) + SELECT * FROM tst WHERE i = 7; +}); + + return decode_json($output)->[0]->{'Plan'}; +} + +# Find the first plan node of the requested type. +sub find_plan_node +{ + my ($plan, $type) = @_; + + return $plan if $plan->{'Node Type'} eq $type; + + foreach my $child (@{ $plan->{'Plans'} // [] }) + { + my $result = find_plan_node($child, $type); + + return $result if defined $result; + } + + return; +} + +# Find the first never-executed plan node of the requested type. +sub find_never_executed_plan_node +{ + my ($plan, $type) = @_; + + return $plan + if $plan->{'Node Type'} eq $type && $plan->{'Actual Loops'} == 0; + + foreach my $child (@{ $plan->{'Plans'} // [] }) + { + my $result = find_never_executed_plan_node($child, $type); + + return $result if defined $result; + } + + return; +} diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 3841f171bb3..eb28a489f48 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -207,8 +207,12 @@ ROLLBACK; nodes report the <glossterm linkend="glossary-buffer-access-strategy">buffer access strategies</glossterm> used by the table scan when the table access - method provides them. The reported list includes every strategy - selected while executing the scan, including across rescans. + method provides them. Executed + <literal>Index Scan</literal>, <literal>Index Only Scan</literal>, and + <literal>Bitmap Index Scan</literal> plan nodes likewise report the + strategies used by the index scan when the index access method provides + them. The reported list includes every strategy selected while + executing the scan, including across rescans. Only strategies used by scan nodes are reported; write-side strategies such as <literal>bulkwrite</literal> are not shown, even when a command uses them internally. diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c index 5059da8dce7..7c9f5021f0b 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -598,6 +598,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm) pgstat_count_index_scan(idxRel); if (scan->instrument) scan->instrument->nsearches++; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); /* * We need to know the size of the table so that we know how long to diff --git a/src/backend/access/gin/ginget.c b/src/backend/access/gin/ginget.c index 2bcb32ca3d0..31595a5a12f 100644 --- a/src/backend/access/gin/ginget.c +++ b/src/backend/access/gin/ginget.c @@ -17,6 +17,7 @@ #include "access/gin_private.h" #include "access/relscan.h" #include "common/pg_prng.h" +#include "executor/instrument_node.h" #include "miscadmin.h" #include "storage/predicate.h" #include "utils/datum.h" @@ -1942,6 +1943,8 @@ gingetbitmap(IndexScanDesc scan, TIDBitmap *tbm) if (GinIsVoidRes(scan)) return 0; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + ntids = 0; /* diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c index 111c310f9df..62b386aa356 100644 --- a/src/backend/access/gist/gistget.c +++ b/src/backend/access/gist/gistget.c @@ -641,6 +641,7 @@ gistgettuple(IndexScanDesc scan, ScanDirection dir) /* Begin the scan by processing the root page */ GISTSearchItem fakeItem; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); pgstat_count_index_scan(scan->indexRelation); if (scan->instrument) scan->instrument->nsearches++; @@ -761,6 +762,7 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm) if (!so->qual_ok) return 0; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); pgstat_count_index_scan(scan->indexRelation); if (scan->instrument) scan->instrument->nsearches++; diff --git a/src/backend/access/hash/hashsearch.c b/src/backend/access/hash/hashsearch.c index 46eb12cc8b2..9955ad26ea2 100644 --- a/src/backend/access/hash/hashsearch.c +++ b/src/backend/access/hash/hashsearch.c @@ -407,6 +407,7 @@ _hash_first(IndexScanDesc scan, ScanDirection dir) hashkey = _hash_datum2hashkey_type(rel, cur->sk_argument, cur->sk_subtype); + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); so->hashso_sk_hash = hashkey; buf = _hash_getbucketbuf_from_hashkey(rel, hashkey, HASH_READ, NULL); diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c index 61f63251c6f..e806a95c110 100644 --- a/src/backend/access/nbtree/nbtsearch.c +++ b/src/backend/access/nbtree/nbtsearch.c @@ -922,6 +922,8 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) !_bt_parallel_seize(scan, &blkno, &lastcurrblkno, true)) return false; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + /* * Initialize the scan's arrays (if any) for the current scan direction * (except when they were already set to later values as part of diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c index 2cc5f06f5d7..5327ab57e0a 100644 --- a/src/backend/access/spgist/spgscan.c +++ b/src/backend/access/spgist/spgscan.c @@ -944,6 +944,9 @@ spggetbitmap(IndexScanDesc scan, TIDBitmap *tbm) so->tbm = tbm; so->ntids = 0; + if (so->searchNulls || so->searchNonNulls) + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + spgWalk(scan->indexRelation, so, true, storeBitmap); return so->ntids; @@ -1065,6 +1068,9 @@ spggettuple(IndexScanDesc scan, ScanDirection dir) } so->iPtr = so->nPtrs = 0; + if (so->searchNulls || so->searchNonNulls) + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + spgWalk(scan->indexRelation, so, false, storeGettuple); if (so->nPtrs == 0) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 735150c859e..2c5a31c2d5b 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -140,6 +140,8 @@ static void show_hashagg_info(AggState *aggstate, ExplainState *es); static void show_indexscan_info(PlanState *planstate, ExplainState *es); static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es); +static void print_buffer_access_strategies(ExplainState *es, + uint32 strategy_mask); static void show_scan_buffer_access_strategies(ScanState *planstate, ExplainState *es); static void show_scan_io_usage(ScanState *planstate, ExplainState *es); @@ -3909,8 +3911,10 @@ show_indexscan_info(PlanState *planstate, ExplainState *es) { Plan *plan = planstate->plan; SharedIndexScanInstrumentation *SharedInfo = NULL; - uint64 nsearches = 0, - ntabletuplefetches = 0; + WorkerNodeInstrumentation *worker_instrument = planstate->worker_instrument; + IndexScanInstrumentation stats = {0}; + double leader_nloops = planstate->instrument ? + planstate->instrument->nloops : 0; if (!es->analyze) return; @@ -3922,7 +3926,7 @@ show_indexscan_info(PlanState *planstate, ExplainState *es) { IndexScanState *indexstate = ((IndexScanState *) planstate); - nsearches = indexstate->iss_Instrument->nsearches; + stats = *indexstate->iss_Instrument; SharedInfo = indexstate->iss_SharedInfo; break; } @@ -3930,8 +3934,7 @@ show_indexscan_info(PlanState *planstate, ExplainState *es) { IndexOnlyScanState *indexstate = ((IndexOnlyScanState *) planstate); - nsearches = indexstate->ioss_Instrument->nsearches; - ntabletuplefetches = indexstate->ioss_Instrument->ntabletuplefetches; + stats = *indexstate->ioss_Instrument; SharedInfo = indexstate->ioss_SharedInfo; break; } @@ -3939,7 +3942,7 @@ show_indexscan_info(PlanState *planstate, ExplainState *es) { BitmapIndexScanState *indexstate = ((BitmapIndexScanState *) planstate); - nsearches = indexstate->biss_Instrument->nsearches; + stats = *indexstate->biss_Instrument; SharedInfo = indexstate->biss_SharedInfo; break; } @@ -3947,22 +3950,50 @@ show_indexscan_info(PlanState *planstate, ExplainState *es) break; } + if (worker_instrument) + { + /* + * After parallel cleanup, the node's loop count includes worker + * loops. Remove them to determine whether the leader executed the + * scan. + */ + for (int i = 0; i < worker_instrument->num_workers; ++i) + leader_nloops -= worker_instrument->instrument[i].nloops; + } + /* Next get the sum of the counters set within each and every process */ if (SharedInfo) { for (int i = 0; i < SharedInfo->num_workers; ++i) { IndexScanInstrumentation *winstrument = &SharedInfo->winstrument[i]; + double worker_nloops = + worker_instrument && i < worker_instrument->num_workers ? + worker_instrument->instrument[i].nloops : 0; + + stats.nsearches += winstrument->nsearches; + stats.ntabletuplefetches += winstrument->ntabletuplefetches; - nsearches += winstrument->nsearches; - ntabletuplefetches += winstrument->ntabletuplefetches; + if (!es->buffers || worker_nloops <= 0 || winstrument->strategy_mask == 0) + continue; + + if (es->workers_state) + ExplainOpenWorker(i, es); + + print_buffer_access_strategies(es, winstrument->strategy_mask); + + if (es->workers_state) + ExplainCloseWorker(i, es); } } + if (es->buffers && stats.strategy_mask != 0 && leader_nloops > 0) + print_buffer_access_strategies(es, stats.strategy_mask); + if (nodeTag(plan) == T_IndexOnlyScan) - ExplainPropertyUInteger("Heap Fetches", NULL, ntabletuplefetches, es); + ExplainPropertyUInteger("Heap Fetches", NULL, stats.ntabletuplefetches, es); - ExplainPropertyUInteger("Index Searches", NULL, nsearches, es); + ExplainPropertyUInteger("Index Searches", NULL, stats.nsearches, es); } /* diff --git a/src/backend/executor/nodeBitmapIndexscan.c b/src/backend/executor/nodeBitmapIndexscan.c index 90b010f9b71..dfea3fdd84c 100644 --- a/src/backend/executor/nodeBitmapIndexscan.c +++ b/src/backend/executor/nodeBitmapIndexscan.c @@ -203,7 +203,7 @@ ExecEndBitmapIndexScan(BitmapIndexScanState *node) * shutdown on the workers. On rescan it will spin up new workers * which will have a new BitmapIndexScanState and zeroed stats. */ - winstrument->nsearches += node->biss_Instrument->nsearches; + CombineIndexScanInstrumentation(winstrument, node->biss_Instrument); Assert(node->biss_Instrument->ntabletuplefetches == 0); } @@ -277,7 +277,11 @@ ExecInitBitmapIndexScan(BitmapIndexScan *node, EState *estate, int eflags) /* Set up instrumentation of bitmap index scans if requested */ if (estate->es_instrument) + { indexstate->biss_Instrument = palloc0_object(IndexScanInstrumentation); + indexstate->biss_Instrument->need_strategy = + (estate->es_instrument & INSTRUMENT_BUFFERS) != 0; + } /* Open the index relation. */ lockmode = exec_rt_fetch(node->scan.scanrelid, estate)->rellockmode; diff --git a/src/backend/executor/nodeIndexonlyscan.c b/src/backend/executor/nodeIndexonlyscan.c index b1009027ac8..14ce758c00f 100644 --- a/src/backend/executor/nodeIndexonlyscan.c +++ b/src/backend/executor/nodeIndexonlyscan.c @@ -260,8 +260,7 @@ ExecEndIndexOnlyScan(IndexOnlyScanState *node) * shutdown on the workers. On rescan it will spin up new workers * which will have a new IndexOnlyScanState and zeroed stats. */ - winstrument->nsearches += node->ioss_Instrument->nsearches; - winstrument->ntabletuplefetches += node->ioss_Instrument->ntabletuplefetches; + CombineIndexScanInstrumentation(winstrument, node->ioss_Instrument); } /* @@ -426,7 +425,11 @@ ExecInitIndexOnlyScan(IndexOnlyScan *node, EState *estate, int eflags) /* Set up instrumentation of index-only scans if requested */ if (estate->es_instrument) + { indexstate->ioss_Instrument = palloc0_object(IndexScanInstrumentation); + indexstate->ioss_Instrument->need_strategy = + (estate->es_instrument & INSTRUMENT_BUFFERS) != 0; + } /* Open the index relation. */ lockmode = exec_rt_fetch(node->scan.scanrelid, estate)->rellockmode; diff --git a/src/backend/executor/nodeIndexscan.c b/src/backend/executor/nodeIndexscan.c index 129d005f187..83262d4ea6e 100644 --- a/src/backend/executor/nodeIndexscan.c +++ b/src/backend/executor/nodeIndexscan.c @@ -819,7 +819,7 @@ ExecEndIndexScan(IndexScanState *node) * shutdown on the workers. On rescan it will spin up new workers * which will have a new IndexOnlyScanState and zeroed stats. */ - winstrument->nsearches += node->iss_Instrument->nsearches; + CombineIndexScanInstrumentation(winstrument, node->iss_Instrument); Assert(node->iss_Instrument->ntabletuplefetches == 0); } @@ -983,7 +983,11 @@ ExecInitIndexScan(IndexScan *node, EState *estate, int eflags) /* Set up instrumentation of index scans if requested */ if (estate->es_instrument) + { indexstate->iss_Instrument = palloc0_object(IndexScanInstrumentation); + indexstate->iss_Instrument->need_strategy = + (estate->es_instrument & INSTRUMENT_BUFFERS) != 0; + } /* Open the index relation. */ lockmode = exec_rt_fetch(node->scan.scanrelid, estate)->rellockmode; diff --git a/src/include/executor/instrument_node.h b/src/include/executor/instrument_node.h index 77abccfe3e1..5b49edc251b 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -150,8 +150,45 @@ typedef struct IndexScanInstrumentation /* Table tuples fetched count (incremented during index-only scans) */ uint64 ntabletuplefetches; + + /* Whether the index AM should record its buffer access strategy */ + bool need_strategy; + + /* Buffer access strategies reported by the index AM */ + uint32 strategy_mask; } IndexScanInstrumentation; +/* + * Record a buffer access strategy selected by an index AM. + * + * Record strategies only when requested by the executor and preserve every + * strategy observed across rescans. + */ +static inline void +InstrRecordIndexScanStrategy(IndexScanInstrumentation *instrument, + BufferAccessStrategyType strategy) +{ + if (!instrument) + return; + + if (instrument->need_strategy) + instrument->strategy_mask |= BufferAccessStrategyBit(strategy); +} + +/* + * Combine index scan instrumentation from 'src' into 'dst'. + * + * Counter values are added and observed strategies are preserved. + */ +static inline void +CombineIndexScanInstrumentation(IndexScanInstrumentation *dst, + IndexScanInstrumentation *src) +{ + dst->nsearches += src->nsearches; + dst->ntabletuplefetches += src->ntabletuplefetches; + dst->strategy_mask |= src->strategy_mask; +} + /* * Shared memory container for per-worker information */ -- 2.34.1
