Hi hackers, While working on [1], I realized that it could be useful to report buffer access strategies in EXPLAIN. This would show whether a scan uses normal buffer replacement or a ring intended to limit cache pollution. Tomas also mentioned this in [2], with support from Andres. So, PFA a five patch series to do so.
=== 0001 Fix an oversight in commit 15f0cb26b530, which left the buffer README describing the bulk-read strategy as using a fixed 256kB ring. It now starts at 256kB and grows according to the configured I/O concurrency. === 0002 Describe the normal, bulkread, bulkwrite, and vacuum strategies in the glossary, and link to these descriptions where appropriate. I think 0001 and 0002 could be committed independently of the discussion around the remaining patches. === 0003 Extract leader and worker table scan instrumentation lookup from show_scan_io_usage(), so that it can be reused by other instrumentation. === 0004 Report the strategy used by sequential, sample, bitmap heap, TID, and TID range scans when ANALYZE and BUFFERS are enabled. This reuses the table scan instrumentation and parallel worker infrastructure introduced by commit 681daed93169 and its follow up commits. A validity flag allows a table AM not to expose the strategy without it being mistaken for BAS_NORMAL. In parallel plans, the strategy is reported only for processes that execute the scan. === 0005 Extend the same reporting to index, index only, and bitmap index scans. The core index AMs report normal, while Bloom reports bulkread because it reads the complete index using a bulk read strategy. 0004 and 0005 include documentation and TAP coverage, including parallel leader/worker reporting. Remark: I initially planned to report more details, including the ring size and ring activity counters. However, after discussing it with Andres at PGConf.EU, we agreed to keep it simple for now and only report the strategy type. [1]: https://postgr.es/m/atHWFmxAz1KuKzqm%40bdtpg [2]: https://postgr.es/m/mu75uaxc2zm7m4izyjucbsfdsy3ehdnueladts2ana5fbirqbv@tnbup5uc5yqc Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From 5b2272d229800c1f30e54abb4f72c004b479c705 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 26 Oct 2026 13:50:58 +0000 Subject: [PATCH v1 1/5] Update bulk-read buffer ring description 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 upate the README: the ring 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. Update the README to describe the current sizing behavior. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ --- src/backend/storage/buffer/README | 33 +++++++++++++++++++------------ 1 file changed, 20 insertions(+), 13 deletions(-) 100.0% src/backend/storage/buffer/ diff --git a/src/backend/storage/buffer/README b/src/backend/storage/buffer/README index b332e002ba1..3d233b53827 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, a ring of at least 256KB 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. +This minimum should also be enough to leave a small cache trail for other +backends to join in a synchronized seq scan. + +The ring 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, its size is +also capped at 1/8th of shared_buffers. + +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 -- 2.34.1
>From 2722bd10c1c4076df8bab63673a5034221631446 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 22 Oct 2026 04:17:46 +0000 Subject: [PATCH v1 2/5] 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: Discussion: https://postgr.es/m/ --- 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.7% doc/src/sgml/ref/ 83.2% doc/src/sgml/ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index f7db2995c3e..8852549e97b 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 @@ -10133,6 +10134,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..66413c838f6 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 strategies are: + </para> + <variablelist> + <varlistentry> + <term><literal>normal</literal></term> + <listitem> + <para> + Uses ordinary buffer replacement without 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 the displacement of unrelated cached data. + </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 3c85846df26..3e3f2d30ec9 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 f2b8022f0e4e9fa2af759d6e7ab26301d6688726 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 26 Oct 2026 15:34:27 +0000 Subject: [PATCH v1 3/5] 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: Discussion: https://postgr.es/m/ --- 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 398037392fc347c3da2419d0d4c421d883f10ad2 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 22 Oct 2026 04:08:08 +0000 Subject: [PATCH v1 4/5] Report buffer access strategy for table scans in EXPLAIN When BUFFERS and ANALYZE are enabled, record the strategy selected by the table scan descriptor for sequential, sample, bitmap heap, TID, and TID range scans, and propagate it through parallel worker instrumentation. Track whether the strategy value is valid so a table AM can leave it unavailable without EXPLAIN confusing it with BAS_NORMAL. Move BufferAccessStrategyType to buf.h so executor instrumentation can use it without including the heavier bufmgr.h. Report the strategy in text and structured output for each executed scan. Add documentation and TAP coverage. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ --- doc/src/sgml/ref/explain.sgml | 7 + src/backend/access/heap/heapam.c | 24 +- src/backend/commands/explain.c | 97 ++++++++ src/backend/executor/nodeBitmapHeapscan.c | 12 +- src/backend/executor/nodeSamplescan.c | 10 +- src/backend/executor/nodeSeqscan.c | 20 +- src/backend/executor/nodeTidrangescan.c | 18 +- src/backend/executor/nodeTidscan.c | 11 +- src/backend/storage/buffer/freelist.c | 14 ++ src/backend/utils/adt/tid.c | 2 +- src/include/access/tableam.h | 12 +- src/include/executor/instrument_node.h | 6 + src/include/storage/buf.h | 15 ++ src/include/storage/bufmgr.h | 16 +- src/test/modules/test_misc/meson.build | 1 + .../t/016_explain_buffer_strategy.pl | 167 +++++++++++++ src/test/regress/expected/explain.out | 231 +++++++++--------- src/test/regress/sql/explain.sql | 2 + 18 files changed, 514 insertions(+), 151 deletions(-) 3.8% src/backend/access/heap/ 10.7% src/backend/commands/ 13.3% src/backend/executor/ 4.2% src/include/storage/ 22.0% src/test/modules/test_misc/t/ 38.6% src/test/regress/expected/ 5.1% src/ diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index f38d31e106a..f7ae8f1b2bd 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -200,6 +200,13 @@ 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 + strategy</glossterm> used by the table scan. 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/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 6a09a1a1517..fd9702abcc5 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -1317,12 +1317,22 @@ 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) + { + scan->rs_base.rs_instrument->strategy = GetAccessStrategyType(scan->rs_strategy); + scan->rs_base.rs_instrument->strategy_valid = true; + } + + /* 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 +1397,12 @@ 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) + { + scan->rs_base.rs_instrument->strategy = GetAccessStrategyType(scan->rs_strategy); + scan->rs_base.rs_instrument->strategy_valid = true; + } } void diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index d0ce72d83ff..7e113a8950c 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_strategy(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_strategy((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_strategy((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_strategy((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_strategy((ScanState *) planstate, es); show_scan_io_usage((ScanState *) planstate, es); } break; @@ -4027,6 +4032,38 @@ 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 the buffer access strategy. + */ +static void +print_buffer_access_strategy(ExplainState *es, + BufferAccessStrategyType strategy) +{ + ExplainPropertyText("Buffer Access Strategy", buffer_access_strategy_name(strategy), es); +} + /* * Print I/O stats - prefetching and I/O performed * @@ -4140,6 +4177,66 @@ 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_strategy(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_valid || + worker_nloops <= 0) + continue; + + if (es->workers_state) + ExplainOpenWorker(i, es); + print_buffer_access_strategy(es, winstrument->strategy); + if (es->workers_state) + ExplainCloseWorker(i, es); + } + } + + if (instrument && instrument->strategy_valid && leader_nloops > 0) + print_buffer_access_strategy(es, instrument->strategy); +} + /* * Show information about prefetch and I/O in a scan node. */ diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c index a9e83f30687..40a561f3c70 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,16 @@ 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; + + AccumulateIOStats(&si->stats.io, &stats->io); + si->stats.strategy = stats->strategy; + si->stats.strategy_valid = stats->strategy_valid; } } diff --git a/src/backend/executor/nodeSamplescan.c b/src/backend/executor/nodeSamplescan.c index f3d273e1c5e..79025f28727 100644 --- a/src/backend/executor/nodeSamplescan.c +++ b/src/backend/executor/nodeSamplescan.c @@ -19,6 +19,7 @@ #include "access/tsmapi.h" #include "common/pg_prng.h" #include "executor/executor.h" +#include "executor/instrument.h" #include "executor/nodeSamplescan.h" #include "utils/fmgrprotos.h" #include "utils/rel.h" @@ -292,6 +293,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 +306,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/nodeSeqscan.c b/src/backend/executor/nodeSeqscan.c index b8c528ca089..7eb46fa3ba6 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 @@ -309,9 +311,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()) { SeqScanInstrumentation *si; @@ -322,6 +322,8 @@ ExecEndSeqScan(SeqScanState *node) if (scanDesc && scanDesc->rs_instrument) { AccumulateIOStats(&si->stats.io, &scanDesc->rs_instrument->io); + si->stats.strategy = scanDesc->rs_instrument->strategy; + si->stats.strategy_valid = scanDesc->rs_instrument->strategy_valid; } } @@ -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..dc001d20e10 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; @@ -362,6 +364,8 @@ ExecEndTidRangeScan(TidRangeScanState *node) if (scan && scan->rs_instrument) { AccumulateIOStats(&si->stats.io, &scan->rs_instrument->io); + si->stats.strategy = scan->rs_instrument->strategy; + si->stats.strategy_valid = scan->rs_instrument->strategy_valid; } } @@ -484,6 +488,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 +535,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 +555,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 +577,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 +602,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/executor/nodeTidscan.c b/src/backend/executor/nodeTidscan.c index b63329de330..28cefb20b8d 100644 --- a/src/backend/executor/nodeTidscan.c +++ b/src/backend/executor/nodeTidscan.c @@ -26,6 +26,7 @@ #include "access/tableam.h" #include "catalog/pg_type.h" #include "executor/executor.h" +#include "executor/instrument.h" #include "executor/nodeTidscan.h" #include "lib/qunique.h" #include "miscadmin.h" @@ -146,9 +147,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; /* 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/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 ea3f2a6be99..2a649431e13 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; /* @@ -1030,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 4759a628afe..31188729835 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,6 +80,10 @@ typedef struct IOStats typedef struct TableScanInstrumentation { IOStats io; + + /* Buffer access strategy reported by the table AM, if available. */ + BufferAccessStrategyType strategy; + bool strategy_valid; } TableScanInstrumentation; /* merge IO statistics from 'src' into 'dst' */ 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); 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..fb48278b81b --- /dev/null +++ b/src/test/modules/test_misc/t/016_explain_buffer_strategy.pl @@ -0,0 +1,167 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +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 Strategy'}, + '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 $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 $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 Strategy'}, + '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 Strategy: 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 Strategy'}, + '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($worker->{'Buffer Access Strategy'}, + 'bulkread', 'parallel worker reports the strategy'); +} + +$node->stop; + +done_testing(); + +sub explain_json +{ + my ($query) = @_; + my $output = $node->safe_psql('postgres', + "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) $query"); + + return decode_json($output)->[0]->{'Plan'}; +} + +sub check_strategy +{ + my ($plan, $strategy, $description) = @_; + + is($plan->{'Buffer Access Strategy'}, + $strategy, "$description uses the $strategy strategy"); +} + +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..1b7a94f4e10 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 Strategy: 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,7 @@ explain_filter <Actual-Rows>N.N</Actual-Rows> <Actual-Loops>N</Actual-Loops> <Disabled>false</Disabled> + <Buffer-Access-Strategy>normal</Buffer-Access-Strategy> <Average-Prefetch-Distance>N.N</Average-Prefetch-Distance> <Max-Prefetch-Distance>N</Max-Prefetch-Distance> <Prefetch-Capacity>N</Prefetch-Capacity> @@ -173,6 +175,7 @@ explain_filter Actual Rows: N.N Actual Loops: N Disabled: false + Buffer Access Strategy: "normal" Average Prefetch Distance: N.N Max Prefetch Distance: N Prefetch Capacity: N @@ -301,65 +304,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 Strategy": "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 +440,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 Strategy": "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 +547,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 strategy, as it depends on shared_buffers + #- '{0,Plan,Plans,0,Plans,0,Buffer Access Strategy}' -- 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 +774,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 Strategy: 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 Strategy: 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..589e14a312a 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 strategy, as it depends on shared_buffers + #- '{0,Plan,Plans,0,Plans,0,Buffer Access Strategy}' -- 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 9249c18d67b99f9d1b53a2aa94c47e2a94cf4c63 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Sat, 24 Oct 2026 09:38:46 +0000 Subject: [PATCH v1 5/5] Report buffer access strategy for index scans in EXPLAIN When ANALYZE and BUFFERS are enabled, report the 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 value and request it only when buffer instrumentation is enabled. Have each in-tree index access method record its selected strategy during scan setup. The standard access methods report normal, while Bloom reports bulkread. Propagate the strategy through parallel worker instrumentation and report it only for the leader and workers that execute the scan. Track whether the value is valid so another index access method can leave it unavailable. Add documentation and TAP coverage for normal and bulkread strategies, parallel workers, never-executed scans, and omission when BUFFERS is disabled. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: https://postgr.es/m/ --- contrib/bloom/blscan.c | 2 + contrib/bloom/meson.build | 1 + contrib/bloom/t/002_explain.pl | 182 +++++++++++++++++++++ doc/src/sgml/ref/explain.sgml | 6 +- src/backend/access/brin/brin.c | 2 + src/backend/access/gin/ginscan.c | 2 + src/backend/access/gist/gistscan.c | 2 + src/backend/access/hash/hash.c | 2 + src/backend/access/nbtree/nbtree.c | 2 + src/backend/access/spgist/spgscan.c | 2 + src/backend/commands/explain.c | 50 ++++-- src/backend/executor/nodeBitmapIndexscan.c | 6 +- src/backend/executor/nodeIndexonlyscan.c | 7 +- src/backend/executor/nodeIndexscan.c | 6 +- src/include/executor/instrument_node.h | 44 +++++ 15 files changed, 301 insertions(+), 15 deletions(-) 51.5% contrib/bloom/t/ 3.5% doc/src/sgml/ref/ 3.7% src/backend/access/ 20.6% src/backend/commands/ 8.0% src/backend/executor/ 11.6% src/include/executor/ diff --git a/contrib/bloom/blscan.c b/contrib/bloom/blscan.c index 1a0e42021ec..3bf71f081de 100644 --- a/contrib/bloom/blscan.c +++ b/contrib/bloom/blscan.c @@ -49,6 +49,8 @@ blrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, { BloomScanOpaque so = (BloomScanOpaque) scan->opaque; + InstrRecordIndexScanStrategy(scan->instrument, BAS_BULKREAD); + if (so->sign) pfree(so->sign); so->sign = NULL; 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..d8c5ad23436 --- /dev/null +++ b/contrib/bloom/t/002_explain.pl @@ -0,0 +1,182 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +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($btree_scan->{'Buffer Access Strategy'}, + 'normal', 'B-tree index scan uses the normal strategy'); + +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 Strategy'}, + '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($heap_scan->{'Buffer Access Strategy'}, + 'normal', 'bitmap heap scan uses the normal strategy'); +ok(defined $index_scan, 'plan contains a bitmap index scan'); +is($index_scan->{'Buffer Access Strategy'}, + 'bulkread', 'bloom index scan uses the bulkread strategy'); + +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 Strategy'}, + '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($worker->{'Buffer Access Strategy'}, + '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 Strategy'}, + '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 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 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 f7ae8f1b2bd..582b53e1aae 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -206,7 +206,11 @@ ROLLBACK; <literal>TID Scan</literal>, and <literal>TID Range Scan</literal> plan nodes report the <glossterm linkend="glossary-buffer-access-strategy">buffer access - strategy</glossterm> used by the table scan. + strategy</glossterm> used by the table scan. Executed + <literal>Index Scan</literal>, <literal>Index Only Scan</literal>, and + <literal>Bitmap Index Scan</literal> plan nodes likewise report the + strategy used by the index scan when the index access method provides + it. 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/access/brin/brin.c b/src/backend/access/brin/brin.c index 5059da8dce7..71607bfcba3 100644 --- a/src/backend/access/brin/brin.c +++ b/src/backend/access/brin/brin.c @@ -964,6 +964,8 @@ void brinrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, ScanKey orderbys, int norderbys) { + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + /* * Other index AMs preprocess the scan keys at this point, or sometime * early during the scan; this lets them optimize by removing redundant diff --git a/src/backend/access/gin/ginscan.c b/src/backend/access/gin/ginscan.c index 1564b409bdb..a786e3521a8 100644 --- a/src/backend/access/gin/ginscan.c +++ b/src/backend/access/gin/ginscan.c @@ -490,6 +490,8 @@ ginrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, { GinScanOpaque so = (GinScanOpaque) scan->opaque; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + ginFreeScanKeys(so); if (scankey && scan->numberOfKeys > 0) diff --git a/src/backend/access/gist/gistscan.c b/src/backend/access/gist/gistscan.c index b67ba5a8a53..aa030ad7bca 100644 --- a/src/backend/access/gist/gistscan.c +++ b/src/backend/access/gist/gistscan.c @@ -133,6 +133,8 @@ gistrescan(IndexScanDesc scan, ScanKey key, int nkeys, int i; MemoryContext oldCxt; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + /* Before leaving current page, deal with any killed items */ if (so->numKilled > 0) gistkillitems(scan); diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c index 63846c10840..504739149b0 100644 --- a/src/backend/access/hash/hash.c +++ b/src/backend/access/hash/hash.c @@ -419,6 +419,8 @@ hashrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, HashScanOpaque so = (HashScanOpaque) scan->opaque; Relation rel = scan->indexRelation; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + if (HashScanPosIsValid(so->currPos)) { /* Before leaving current page, deal with any killed items */ diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index 0abdd7b49f5..9e483d2f78f 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -390,6 +390,8 @@ btrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, { BTScanOpaque so = (BTScanOpaque) scan->opaque; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + /* we aren't holding any read locks, but gotta drop the pins */ if (BTScanPosIsValid(so->currPos)) { diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c index 2cc5f06f5d7..87d3df64dd3 100644 --- a/src/backend/access/spgist/spgscan.c +++ b/src/backend/access/spgist/spgscan.c @@ -377,6 +377,8 @@ spgrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, { SpGistScanOpaque so = (SpGistScanOpaque) scan->opaque; + InstrRecordIndexScanStrategy(scan->instrument, BAS_NORMAL); + /* copy scankeys into local storage */ if (scankey && scan->numberOfKeys > 0) memcpy(scan->keyData, scankey, scan->numberOfKeys * sizeof(ScanKeyData)); diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 7e113a8950c..418919c8a4a 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_strategy(ExplainState *es, + BufferAccessStrategyType strategy); static void show_scan_buffer_access_strategy(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,49 @@ 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; - nsearches += winstrument->nsearches; - ntabletuplefetches += winstrument->ntabletuplefetches; + stats.nsearches += winstrument->nsearches; + stats.ntabletuplefetches += winstrument->ntabletuplefetches; + + if (!es->buffers || worker_nloops <= 0 || + !winstrument->strategy_valid) + continue; + + if (es->workers_state) + ExplainOpenWorker(i, es); + print_buffer_access_strategy(es, winstrument->strategy); + if (es->workers_state) + ExplainCloseWorker(i, es); } } + if (es->buffers && stats.strategy_valid && leader_nloops > 0) + print_buffer_access_strategy(es, stats.strategy); + 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..86e4eb39923 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; + AccumulateIndexScanInstrumentation(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..520a166c891 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; + AccumulateIndexScanInstrumentation(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..429f5567f53 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; + AccumulateIndexScanInstrumentation(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 31188729835..36f9e2cf1cb 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -114,8 +114,52 @@ 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 strategy reported by the index AM, if available */ + BufferAccessStrategyType strategy; + bool strategy_valid; } IndexScanInstrumentation; +/* + * Record the buffer access strategy selected by an index AM. + * + * The strategy is recorded only when requested by the executor. + */ +static inline void +InstrRecordIndexScanStrategy(IndexScanInstrumentation *instrument, + BufferAccessStrategyType strategy) +{ + if (!instrument) + return; + + if (instrument->need_strategy) + { + Assert(!instrument->strategy_valid || + instrument->strategy == strategy); + instrument->strategy = strategy; + instrument->strategy_valid = true; + } +} + +/* Merge index scan instrumentation from 'src' into 'dst'. */ +static inline void +AccumulateIndexScanInstrumentation(IndexScanInstrumentation *dst, + IndexScanInstrumentation *src) +{ + dst->nsearches += src->nsearches; + dst->ntabletuplefetches += src->ntabletuplefetches; + + if (src->strategy_valid) + { + Assert(!dst->strategy_valid || dst->strategy == src->strategy); + dst->strategy = src->strategy; + dst->strategy_valid = true; + } +} + /* * Shared memory container for per-worker information */ -- 2.34.1
