Hi, On Tue, Oct 27, 2026 at 01:56:16PM -0500, Sami Imseih wrote: > Hi, > > > While working on [1], I realized that it could be useful to report buffer > > access strategies in EXPLAIN. This would show whether a scan uses normal > > buffer replacement or a ring intended to limit cache pollution. Tomas also > > mentioned > > this in [2], with support from Andres. So, PFA a five patch series to do so. > > +1. I think this is a good idea. BufferAccessStrategy has historically been > a fairly hidden part of buffer management, and loosely documented from a > user perspective. With pg_stat_io exposing it, it seems logical to me to also > include it in EXPLAIN (ANALYZE, BUFFERS). > > I took a look at the patchset.
Thanks for looking at it! > v1-0001: > > Maybe: > > "The ring is enlarged beyond the 256KB minimum by up to io_combine_limit *" > > That makes it clear that 256KB is the minimum. I don't think 256KB is always the minimum, as GetAccessStrategyWithSize() caps the actual ring size at 1/8th of shared_buffers. For example, with shared_buffers set to 1MB, the ring contains 128KB. I've reworded this part in v2 to distinguish the requested size from the actual size. > v1-0002: > > Looks good overall. One wording suggestion: > > Maybe: > > "buffers avoids evicting unrelated pages from shared buffers." > > That seems closer to the usual shared-buffer terminology. Yeah, that wording is clearer. I used "reduces eviction" though, as the strategy does not necessarily avoid such evictions entirely. > Some additional documentation points: > > In my recent talk at PG Summit NY, someone asked whether a page that is > already in shared buffers would be read again if it is later read through a > ring. My answer was no, and I think that is right. Yeah, that's right. > But then I noticed that the pg_stat_io docs describe the bulkread/bulkwrite/ > vacuum contexts as I/O done "outside of shared buffers" [1]. I think that is > easy to misread. Strategy rings still use shared buffers; they are not > separate. If a page is already in shared buffers, a later bulkread access can > hit it there rather than reading it again. The existing glossary already describes this correctly, but the pg_stat_io descriptions does not. Fixed in a new dedicated 0003. > Also, src/backend/storage/buffer/README says bulk writes currently apply only > to COPY IN and CREATE TABLE AS SELECT. That looks outdated now that > GetBulkInsertState() is also used by CREATE MATERIALIZED VIEW, REFRESH > MATERIALIZED VIEW, and table rewrite paths. > Maybe we should clarify these in this patchset as well? Indeed, done in 0001 while at it. I made the list non exhaustive and changed it to mention COPY FROM, CREATE TABLE AS, materialized view population, and some table rewrites. I also took care of the other comments and ended up with this split: 0004 refactors the table scan instrumentation lookup. 0005 adds the common buffer strategy instrumentation. 0006 adds the missing per worker instrumentation for SampleScan and TidScan, fixing the reported reproducer (nice catch, thanks!) 0007 reports strategies for all supported table scan types. 0008 reports strategies for index scans. I removed the rescan assertion and now retain the most recently reported strategy, as on the table AM side. Reporting mixed would be more precise if an extension changes strategy on rescan, but I preferred to keep it simple for now since in core AMs do not do that. The accumulation helpers have been renamed to CombineTableScanInstrumentation() and CombineIndexScanInstrumentation(), and index only scan coverage has been added. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From 99b4f39ce43b2b34f51a2fb64f72e5d70d14c305 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 26 Oct 2026 13:50:58 +0000 Subject: [PATCH v2 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 795e4bc0feaa42f9fe3dc35bca2d96b45b073224 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 22 Oct 2026 04:17:46 +0000 Subject: [PATCH v2 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.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..b4353d33b74 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 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 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 752e82edbdf03ace7297aca300bee5a3a193dbc0 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 09:42:48 +0000 Subject: [PATCH v2 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 31ff4a9d99f..c62c7caa66a 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</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 dbefa05f1cc49da329172d224f19d0ae80caecc8 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Mon, 26 Oct 2026 15:34:27 +0000 Subject: [PATCH v2 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 0929158ba964c53d84a873c57144c008f4b0ef25 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 06:49:11 +0000 Subject: [PATCH v2 5/8] Add buffer access strategy to table scan instrumentation Extend table scan instrumentation with an optional buffer access strategy and have the heap table AM record it when requested. Track whether the value is available so another table AM can leave it unset without it being mistaken for BAS_NORMAL. Propagate the strategy 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 | 24 ++++++++++++++++++---- src/backend/executor/nodeBitmapHeapscan.c | 10 ++++++--- src/backend/executor/nodeSeqscan.c | 22 ++++++++++++-------- src/backend/executor/nodeTidrangescan.c | 20 +++++++++++------- src/backend/storage/buffer/freelist.c | 14 +++++++++++++ src/include/access/tableam.h | 4 +++- src/include/executor/instrument_node.h | 25 +++++++++++++++++++++++ src/include/storage/buf.h | 15 ++++++++++++++ src/include/storage/bufmgr.h | 16 +-------------- 9 files changed, 111 insertions(+), 39 deletions(-) 16.7% src/backend/access/heap/ 45.5% src/backend/executor/ 5.2% src/backend/storage/buffer/ 3.1% src/include/access/ 10.8% src/include/executor/ 18.4% src/include/storage/ 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/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..fbf44729ff5 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; @@ -320,9 +320,7 @@ ExecEndSeqScan(SeqScanState *node) si = &node->sinstrument->sinstrument[ParallelWorkerNumber]; if (scanDesc && scanDesc->rs_instrument) - { - AccumulateIOStats(&si->stats.io, &scanDesc->rs_instrument->io); - } + CombineTableScanInstrumentation(&si->stats, scanDesc->rs_instrument); } /* @@ -400,6 +398,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 +445,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 +463,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 +484,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 +509,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..83fc7142e7b 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; @@ -360,9 +362,7 @@ ExecEndTidRangeScan(TidRangeScanState *node) si = &node->trss_sinstrument->sinstrument[ParallelWorkerNumber]; if (scan && scan->rs_instrument) - { - AccumulateIOStats(&si->stats.io, &scan->rs_instrument->io); - } + CombineTableScanInstrumentation(&si->stats, scan->rs_instrument); } if (scan != NULL) @@ -484,6 +484,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 +531,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 +551,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 +573,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 +598,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 4759a628afe..845c18e7aa1 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' */ @@ -96,6 +102,25 @@ 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, while the strategy from 'src' replaces the + * strategy in 'dst' when available. + */ +static inline void +CombineTableScanInstrumentation(TableScanInstrumentation *dst, + TableScanInstrumentation *src) +{ + AccumulateIOStats(&dst->io, &src->io); + + if (src->strategy_valid) + { + dst->strategy = src->strategy; + dst->strategy_valid = true; + } +} + /* --------------------- * 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 707ccce1f082fad2c7313801c0fd01df4ba56dff Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 06:49:21 +0000 Subject: [PATCH v2 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(-) 76.1% src/backend/executor/ 3.3% src/include/access/ 17.3% src/include/executor/ 3.1% src/ diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c index 316dd3f67c6..c429570e9a7 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 845c18e7aa1..005fdc6f57b 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -335,6 +335,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; + 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; + 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 fe8681ba642..2e1d584096b 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1672,6 +1672,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; /* @@ -1874,6 +1875,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 cfef572df80..a7b1035f068 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 6e85d09e19e949b9d8b62150703d5a7aa4457d13 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 28 Oct 2026 06:49:22 +0000 Subject: [PATCH v2 7/8] Report buffer access strategy for table scans in EXPLAIN When ANALYZE and BUFFERS are enabled, report the buffer access strategy selected by sequential, sample, bitmap heap, TID, and TID range scans. Read the strategy from leader and per-worker table scan instrumentation and report it only for processes that execute the scan. Add documentation and TAP coverage for serial and parallel execution, 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 | 7 + src/backend/commands/explain.c | 115 +++++++++ src/test/modules/test_misc/meson.build | 1 + .../t/016_explain_buffer_strategy.pl | 209 ++++++++++++++++ src/test/regress/expected/explain.out | 231 +++++++++--------- src/test/regress/sql/explain.sql | 2 + 6 files changed, 454 insertions(+), 111 deletions(-) 15.8% src/backend/commands/ 33.6% src/test/modules/test_misc/t/ 47.4% src/test/regress/expected/ 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/commands/explain.c b/src/backend/commands/explain.c index d0ce72d83ff..c2a30077b4a 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 * @@ -4111,6 +4148,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 +4166,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 +4195,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/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..67b995a10e2 --- /dev/null +++ b/src/test/modules/test_misc/t/016_explain_buffer_strategy.pl @@ -0,0 +1,209 @@ +# 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 $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 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 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 Strategy'}, + "$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($worker->{'Buffer Access Strategy'}, + $strategy, "$description worker 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 5cfd87b80e642b90072fb77411f8697aa346355d Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Sat, 24 Oct 2026 09:38:46 +0000 Subject: [PATCH v2 8/8] 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 core 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: Sami Imseih <[email protected]> Discussion: https://postgr.es/m/auCYYbDyrQEwIsQq@bdtpg --- contrib/bloom/blscan.c | 2 + contrib/bloom/meson.build | 1 + contrib/bloom/t/002_explain.pl | 204 +++++++++++++++++++++ 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 | 47 +++++ 15 files changed, 326 insertions(+), 15 deletions(-) 54.6% contrib/bloom/t/ 3.2% doc/src/sgml/ref/ 3.4% src/backend/access/ 19.2% src/backend/commands/ 7.4% src/backend/executor/ 10.9% 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..312e2b8b8f5 --- /dev/null +++ b/contrib/bloom/t/002_explain.pl @@ -0,0 +1,204 @@ +# 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 $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($btree_index_only_scan->{'Buffer Access Strategy'}, + 'normal', 'B-tree index-only 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 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 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 c2a30077b4a..1f35efa0412 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..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 005fdc6f57b..68888e49b08 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -133,8 +133,55 @@ 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; + + /* Most recently reported buffer access strategy, 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. The most + * recently reported strategy is retained. + */ +static inline void +InstrRecordIndexScanStrategy(IndexScanInstrumentation *instrument, + BufferAccessStrategyType strategy) +{ + if (!instrument) + return; + + if (instrument->need_strategy) + { + instrument->strategy = strategy; + instrument->strategy_valid = true; + } +} + +/* + * Combine index scan instrumentation from 'src' into 'dst'. + * + * Counter values are added, while the strategy from 'src' replaces the + * strategy in 'dst' when available. + */ +static inline void +CombineIndexScanInstrumentation(IndexScanInstrumentation *dst, + IndexScanInstrumentation *src) +{ + dst->nsearches += src->nsearches; + dst->ntabletuplefetches += src->ntabletuplefetches; + + if (src->strategy_valid) + { + dst->strategy = src->strategy; + dst->strategy_valid = true; + } +} + /* * Shared memory container for per-worker information */ -- 2.34.1
