Subject: Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE

From: Guillaume Lelarge <[email-masked]>
Date: Wed, 20 Nov 2024 07:26:37 +0100

Le mer. 20 nov. 2024 à 01:13, Greg Sabino Mullane <[email-masked]> a
écrit :

> I'm with Robert on this one. I'm a grudging +1 to buffers defaulting on,
> and a strong -1 to all the other proposals. Guillaume, the patch looks
> pretty good.
>

Thanks :)

> I would like to see some of the example output have more than just "shared
> hit" and "read" though: let's throw some "dirtied" and "written" in
> there as well.
>
>
OK, but I'm not sure which example I should pick to add dirtied and written
shared buffers. It looks kinda artificial. Should I choose one randomly?

It defaults to <literal>TRUE</literal> when <literal>ANALYZE</literal> is
>> also enabled. Otherwise, it defaults to <literal>FALSE</literal>.
>
>
> Is that second sentence really needed? Because "BUFFERS ON" will never be
> needed anymore (save as a no-op to allow the same explain queries to run
> cross-version), and BUFFERS OFF outside of analyze is meaningless.
>
>
Done in v3 with David's idea.

-- 
Guillaume.
From 5b815f71e874522bcc5947a29623328fdc76104b Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge <[email-masked]>
Date: Tue, 12 Nov 2024 21:59:14 +0100
Subject: [PATCH v3] Enable BUFFERS by default with EXPLAIN ANALYZE

This automatically enables the BUFFER option when ANALYZE is used with
EXPLAIN. The BUFFER option stays disabled if ANALYZE isn't used.

The auto_explain extension gets the same treatment.
---
 contrib/auto_explain/auto_explain.c           |  4 +-
 doc/src/sgml/auto-explain.sgml                |  2 +-
 doc/src/sgml/perform.sgml                     | 42 ++++++---
 doc/src/sgml/ref/explain.sgml                 |  6 +-
 src/backend/commands/explain.c                |  7 ++
 src/test/regress/expected/brin_multi.out      | 18 ++--
 src/test/regress/expected/explain.out         | 36 ++++++-
 .../regress/expected/incremental_sort.out     |  4 +-
 src/test/regress/expected/memoize.out         |  2 +-
 src/test/regress/expected/merge.out           |  2 +-
 src/test/regress/expected/partition_prune.out | 94 +++++++++----------
 src/test/regress/expected/select.out          |  2 +-
 src/test/regress/expected/select_into.out     |  4 +-
 src/test/regress/expected/select_parallel.out |  6 +-
 src/test/regress/expected/subselect.out       |  2 +-
 src/test/regress/expected/tidscan.out         |  6 +-
 src/test/regress/sql/brin_multi.sql           | 18 ++--
 src/test/regress/sql/explain.sql              |  7 +-
 src/test/regress/sql/incremental_sort.sql     |  4 +-
 src/test/regress/sql/memoize.sql              |  2 +-
 src/test/regress/sql/merge.sql                |  2 +-
 src/test/regress/sql/partition_prune.sql      | 94 +++++++++----------
 src/test/regress/sql/select.sql               |  2 +-
 src/test/regress/sql/select_into.sql          |  4 +-
 src/test/regress/sql/select_parallel.sql      |  6 +-
 src/test/regress/sql/subselect.sql            |  2 +-
 src/test/regress/sql/tidscan.sql              |  6 +-
 27 files changed, 220 insertions(+), 164 deletions(-)

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 623a674f99..484e009577 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -27,7 +27,7 @@ static int	auto_explain_log_min_duration = -1; /* msec or -1 */
 static int	auto_explain_log_parameter_max_length = -1; /* bytes or -1 */
 static bool auto_explain_log_analyze = false;
 static bool auto_explain_log_verbose = false;
-static bool auto_explain_log_buffers = false;
+static bool auto_explain_log_buffers = true;
 static bool auto_explain_log_wal = false;
 static bool auto_explain_log_triggers = false;
 static bool auto_explain_log_timing = true;
@@ -152,7 +152,7 @@ _PG_init(void)
 							 "Log buffers usage.",
 							 NULL,
 							 &auto_explain_log_buffers,
-							 false,
+							 true,
 							 PGC_SUSET,
 							 0,
 							 NULL,
diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml
index 0c4656ee30..6623d36c99 100644
--- a/doc/src/sgml/auto-explain.sgml
+++ b/doc/src/sgml/auto-explain.sgml
@@ -122,7 +122,7 @@ LOAD 'auto_explain';
       equivalent to the <literal>BUFFERS</literal> option of <command>EXPLAIN</command>.
       This parameter has no effect
       unless <varname>auto_explain.log_analyze</varname> is enabled.
-      This parameter is off by default.
+      This parameter is on by default.
       Only superusers can change this setting.
      </para>
     </listitem>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index cd12b9ce48..b353cc77b6 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -722,13 +722,19 @@ WHERE t1.unique1 &lt; 10 AND t1.unique2 = t2.unique2;
                                                            QUERY PLAN
 -------------------------------------------------------------------&zwsp;--------------------------------------------------------------
  Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1)
+   Buffers: shared hit=36 read=6
    -&gt;  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1)
          Recheck Cond: (unique1 &lt; 10)
          Heap Blocks: exact=10
+         Buffers: shared hit=12
          -&gt;  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1)
                Index Cond: (unique1 &lt; 10)
+               Buffers: shared hit=2
    -&gt;  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10)
          Index Cond: (unique2 = t1.unique2)
+         Buffers: shared hit=24 read=6
+ Planning:
+   Buffers: shared hit=15
  Planning Time: 0.485 ms
  Execution Time: 0.073 ms
 </screen>
@@ -769,16 +775,24 @@ WHERE t1.unique1 &lt; 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
  Sort  (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1)
    Sort Key: t1.fivethous
    Sort Method: quicksort  Memory: 74kB
+   Buffers: shared hit=440
    -&gt;  Hash Join  (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1)
          Hash Cond: (t2.unique2 = t1.unique2)
+         Buffers: shared hit=437
          -&gt;  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1)
+               Buffers: shared hit=345
          -&gt;  Hash  (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 35kB
+               Buffers: shared hit=92
                -&gt;  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1)
                      Recheck Cond: (unique1 &lt; 100)
                      Heap Blocks: exact=90
+                     Buffers: shared hit=92
                      -&gt;  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                            Index Cond: (unique1 &lt; 100)
+                           Buffers: shared hit=2
+ Planning:
+   Buffers: shared hit=12
  Planning Time: 0.187 ms
  Execution Time: 3.036 ms
 </screen>
@@ -803,6 +817,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten &lt; 7;
  Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1)
    Filter: (ten &lt; 7)
    Rows Removed by Filter: 3000
+   Buffers: shared hit=345
  Planning Time: 0.102 ms
  Execution Time: 2.145 ms
 </screen>
@@ -826,6 +841,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
  Seq Scan on polygon_tbl  (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1)
    Filter: (f1 @&gt; '((0.5,2))'::polygon)
    Rows Removed by Filter: 7
+   Buffers: shared hit=1
  Planning Time: 0.039 ms
  Execution Time: 0.033 ms
 </screen>
@@ -845,6 +861,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
  Index Scan using gpolygonind on polygon_tbl  (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1)
    Index Cond: (f1 @&gt; '((0.5,2))'::polygon)
    Rows Removed by Index Recheck: 1
+   Buffers: shared hit=3
  Planning Time: 0.039 ms
  Execution Time: 0.098 ms
 </screen>
@@ -857,34 +874,27 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @&gt; polygon '(0.5,2.0)';
    </para>
 
    <para>
-    <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with
-    <literal>ANALYZE</literal> to get even more run time statistics:
+    <command>EXPLAIN</command> has a <literal>BUFFERS</literal> option which is
+    enabled by default when <literal>ANALYZE</literal> is used. The numbers provided
+    by <literal>BUFFERS</literal> help to identify which parts of the query are the
+    most I/O-intensive. You can turn it off:
 
 <screen>
-EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000;
+EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000;
 
                                                            QUERY PLAN
 -------------------------------------------------------------------&zwsp;--------------------------------------------------------------
  Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1)
    Recheck Cond: ((unique1 &lt; 100) AND (unique2 &gt; 9000))
    Heap Blocks: exact=10
-   Buffers: shared hit=14 read=3
    -&gt;  BitmapAnd  (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1)
-         Buffers: shared hit=4 read=3
          -&gt;  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1)
                Index Cond: (unique1 &lt; 100)
-               Buffers: shared hit=2
          -&gt;  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1)
                Index Cond: (unique2 &gt; 9000)
-               Buffers: shared hit=2 read=3
- Planning:
-   Buffers: shared hit=3
  Planning Time: 0.162 ms
  Execution Time: 0.143 ms
 </screen>
-
-    The numbers provided by <literal>BUFFERS</literal> help to identify which parts
-    of the query are the most I/O-intensive.
    </para>
 
    <para>
@@ -906,8 +916,12 @@ EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 &lt; 100;
    -&gt;  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1)
          Recheck Cond: (unique1 &lt; 100)
          Heap Blocks: exact=90
+         Buffers: shared hit=92
          -&gt;  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1)
                Index Cond: (unique1 &lt; 100)
+               Buffers: shared hit=2
+ Planning:
+   Buffers: shared hit=91
  Planning Time: 0.151 ms
  Execution Time: 1.856 ms
 
@@ -1040,10 +1054,14 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000
                                                           QUERY PLAN
 -------------------------------------------------------------------&zwsp;------------------------------------------------------------
  Limit  (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1)
+   Buffers: shared hit=16
    -&gt;  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1)
          Index Cond: (unique2 &gt; 9000)
          Filter: (unique1 &lt; 100)
          Rows Removed by Filter: 287
+         Buffers: shared hit=16
+ Planning:
+   Buffers: shared hit=49
  Planning Time: 0.077 ms
  Execution Time: 0.086 ms
 </screen>
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index db9d3a8549..4d6f655b03 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -201,8 +201,10 @@ ROLLBACK;
       query processing.
       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.  This parameter defaults to
-      <literal>FALSE</literal>.
+      format, only non-zero values are printed.
+      Buffers information is included by default when
+      <literal>ANALYZE</literal> is used but otherwise is not included by
+      default, but can be enabled using this option.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7c0fd63b2f..d6815e01d1 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -198,6 +198,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	List	   *rewritten;
 	ListCell   *lc;
 	bool		timing_set = false;
+	bool		buffers_set = false;
 	bool		summary_set = false;
 
 	/* Parse options list. */
@@ -212,7 +213,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 		else if (strcmp(opt->defname, "costs") == 0)
 			es->costs = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "buffers") == 0)
+		{
+			buffers_set = true;
 			es->buffers = defGetBoolean(opt);
+		}
 		else if (strcmp(opt->defname, "wal") == 0)
 			es->wal = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "settings") == 0)
@@ -292,6 +296,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	/* if the timing was not set explicitly, set default value */
 	es->timing = (timing_set) ? es->timing : es->analyze;
 
+	/* if the buffers was not set explicitly, set default value */
+	es->buffers = (buffers_set) ? es->buffers : es->analyze;
+
 	/* check that timing is used with EXPLAIN ANALYZE */
 	if (es->timing && !es->analyze)
 		ereport(ERROR,
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index ae9ce9d8ec..f2d1465818 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -845,7 +845,7 @@ INSERT INTO brin_date_test SELECT '5874897-12-01'::date + i FROM generate_series
 CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
 SET enable_seqscan = off;
 -- make sure the ranges were built correctly and 2023-01-01 eliminates all
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -864,7 +864,7 @@ INSERT INTO brin_timestamp_test
 SELECT i FROM generate_series('2000-01-01'::timestamp, '2000-02-09'::timestamp, '1 day'::interval) s(i);
 CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
 SET enable_seqscan = off;
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
                                   QUERY PLAN                                  
 ------------------------------------------------------------------------------
@@ -874,7 +874,7 @@ SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
          Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
 (4 rows)
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
                                   QUERY PLAN                                  
 ------------------------------------------------------------------------------
@@ -892,7 +892,7 @@ INSERT INTO brin_date_test VALUES ('-infinity'), ('infinity');
 INSERT INTO brin_date_test SELECT '2000-01-01'::date + i FROM generate_series(1, 40) s(i);
 CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
 SET enable_seqscan = off;
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -902,7 +902,7 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
          Index Cond: (a = '2023-01-01'::date)
 (4 rows)
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -921,7 +921,7 @@ INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_se
 INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series( 177999980,  178000000) s(i);
 CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
 SET enable_seqscan = off;
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
                                  QUERY PLAN                                  
 -----------------------------------------------------------------------------
@@ -931,7 +931,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
          Index Cond: (a = '@ 30 years ago'::interval)
 (4 rows)
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
                                  QUERY PLAN                                  
 -----------------------------------------------------------------------------
@@ -949,7 +949,7 @@ INSERT INTO brin_interval_test VALUES ('-infinity'), ('infinity');
 INSERT INTO brin_interval_test SELECT (i || ' days')::interval FROM generate_series(100, 140) s(i);
 CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
 SET enable_seqscan = off;
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
                                  QUERY PLAN                                  
 -----------------------------------------------------------------------------
@@ -959,7 +959,7 @@ SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
          Index Cond: (a = '@ 30 years ago'::interval)
 (4 rows)
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
                                  QUERY PLAN                                  
 -----------------------------------------------------------------------------
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index d2eef8097c..930b4c6f48 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -77,7 +77,7 @@ select explain_filter('explain (analyze, verbose) select * from int8_tbl i8');
  Execution Time: N.N ms
 (4 rows)
 
-select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8');
+select explain_filter('explain (analyze, format text) 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 loops=N)
@@ -85,7 +85,7 @@ select explain_filter('explain (analyze, buffers, format text) select * from int
  Execution Time: N.N ms
 (3 rows)
 
-select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8');
+select explain_filter('explain (analyze, format xml) select * from int8_tbl i8');
                      explain_filter                     
 --------------------------------------------------------
  <explain xmlns="http://www.postgresql.org/N/explain"> +
@@ -136,7 +136,15 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8
  </explain>
 (1 row)
 
-select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8');
+select explain_filter('explain (analyze, buffers off, format text) 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 loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(3 rows)
+
+select explain_filter('explain (analyze, serialize, format yaml) select * from int8_tbl i8');
         explain_filter         
 -------------------------------
  - Plan:                      +
@@ -400,9 +408,29 @@ select explain_filter('explain (memory, analyze, format json) select * from int8
        "Actual Total Time": N.N,   +
        "Actual Rows": N,           +
        "Actual Loops": N,          +
-       "Disabled": false           +
+       "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       +
      },                            +
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 2df7a5db12..d597575840 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -39,7 +39,7 @@ declare
   line text;
 begin
   for line in
-    execute 'explain (analyze, costs off, summary off, timing off) ' || query
+    execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query
   loop
     out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g');
     return next;
@@ -55,7 +55,7 @@ declare
   element jsonb;
   matching_nodes jsonb := '[]'::jsonb;
 begin
-  execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements;
+  execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements;
   while jsonb_array_length(elements) > 0 loop
     element := elements->0;
     elements := elements - 0;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index f6b8329cd6..5ecf971dad 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -10,7 +10,7 @@ declare
     ln text;
 begin
     for ln in
-        execute format('explain (analyze, costs off, summary off, timing off) %s',
+        execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
             query)
     loop
         if hide_hitmiss = true then
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 521d70a891..28d8551063 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1621,7 +1621,7 @@ $$
 DECLARE ln text;
 BEGIN
     FOR ln IN
-        EXECUTE 'explain (analyze, timing off, summary off, costs off) ' ||
+        EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' ||
 		  query
     LOOP
         ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*',  '\1: xxx', 'g');
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7a03b4e360..c710f316d7 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -11,7 +11,7 @@ declare
     ln text;
 begin
     for ln in
-        execute format('explain (analyze, costs off, summary off, timing off) %s',
+        execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
             query)
     loop
         ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N');
@@ -2127,7 +2127,7 @@ create table ab_a3_b3 partition of ab_a3 for values in (3);
 set enable_indexonlyscan = off;
 prepare ab_q1 (int, int, int) as
 select * from ab where a between $1 and $2 and b <= $3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3);
                        QUERY PLAN                        
 ---------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2140,7 +2140,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
          Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
 (8 rows)
 
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3);
                        QUERY PLAN                        
 ---------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2163,7 +2163,7 @@ deallocate ab_q1;
 -- Runtime pruning after optimizer pruning
 prepare ab_q1 (int, int) as
 select a from ab where a between $1 and $2 and b < 3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2);
                        QUERY PLAN                        
 ---------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2174,7 +2174,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
          Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
 (6 rows)
 
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4);
                        QUERY PLAN                        
 ---------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2193,7 +2193,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
 -- different levels of partitioning.
 prepare ab_q2 (int, int) as
 select a from ab where a between $1 and $2 and b < (select 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2);
                               QUERY PLAN                               
 -----------------------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2211,7 +2211,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
 -- As above, but swap the PARAM_EXEC Param to the first partition level
 prepare ab_q3 (int, int) as
 select a from ab where b between $1 and $2 and a < (select 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2);
                               QUERY PLAN                               
 -----------------------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2273,7 +2273,7 @@ begin;
 -- Test run-time pruning using stable functions
 create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
 -- Ensure pruning works using a stable function containing no Vars
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
+explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1);
                             QUERY PLAN                            
 ------------------------------------------------------------------
  Append (actual rows=1 loops=1)
@@ -2283,7 +2283,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
 (4 rows)
 
 -- Ensure pruning does not take place when the function has a Var parameter
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
+explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a);
                             QUERY PLAN                            
 ------------------------------------------------------------------
  Append (actual rows=4 loops=1)
@@ -2298,7 +2298,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
 (9 rows)
 
 -- Ensure pruning does not take place when the expression contains a Var.
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
+explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a;
                             QUERY PLAN                            
 ------------------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2334,7 +2334,7 @@ declare
     ln text;
 begin
     for ln in
-        execute format('explain (analyze, costs off, summary off, timing off) %s',
+        execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
             $1)
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
@@ -2641,7 +2641,7 @@ reset parallel_tuple_cost;
 reset min_parallel_table_scan_size;
 reset max_parallel_workers_per_gather;
 -- Test run-time partition pruning with an initplan
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
@@ -2700,7 +2700,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1
 (52 rows)
 
 -- Test run-time partition pruning with UNION ALL parents
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
                                   QUERY PLAN                                   
 -------------------------------------------------------------------------------
@@ -2744,7 +2744,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
 (37 rows)
 
 -- A case containing a UNION ALL with a non-partitioned child.
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
                                   QUERY PLAN                                   
 -------------------------------------------------------------------------------
@@ -2803,7 +2803,7 @@ union all
 	select tableoid::regclass,a,b from ab
 ) ab where a = $1 and b = (select -10);
 -- Ensure the xy_1 subplan is not pruned.
-explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1);
                        QUERY PLAN                       
 --------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -2952,7 +2952,7 @@ create index tprt6_idx on tprt_6 (col1);
 insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
 set enable_hashjoin = off;
 set enable_mergejoin = off;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
                                 QUERY PLAN                                
 --------------------------------------------------------------------------
@@ -2973,7 +2973,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
                Index Cond: (col1 < tbl1.col1)
 (15 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
                                 QUERY PLAN                                
 --------------------------------------------------------------------------
@@ -3018,7 +3018,7 @@ order by tbl1.col1, tprt.col1;
 
 -- Multiple partitions
 insert into tbl1 values (1001), (1010), (1011);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
                                 QUERY PLAN                                
 --------------------------------------------------------------------------
@@ -3039,7 +3039,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
                Index Cond: (col1 < tbl1.col1)
 (15 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
                                 QUERY PLAN                                
 --------------------------------------------------------------------------
@@ -3103,7 +3103,7 @@ order by tbl1.col1, tprt.col1;
 -- Last partition
 delete from tbl1;
 insert into tbl1 values (4400);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
                                 QUERY PLAN                                
 --------------------------------------------------------------------------
@@ -3135,7 +3135,7 @@ order by tbl1.col1, tprt.col1;
 -- No matching partition
 delete from tbl1;
 insert into tbl1 values (10000);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
                             QUERY PLAN                             
 -------------------------------------------------------------------
@@ -3175,7 +3175,7 @@ alter table part_cab attach partition part_abc_p1 for values in(3);
 prepare part_abc_q1 (int, int, int) as
 select * from part_abc where a = $1 and b = $2 and c = $3;
 -- Single partition should be scanned.
-explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3);
                         QUERY PLAN                        
 ----------------------------------------------------------
  Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1)
@@ -3200,7 +3200,7 @@ select * from listp where b = 1;
 -- partitions before finally detecting the correct set of 2nd level partitions
 -- which match the given parameter.
 prepare q1 (int,int) as select * from listp where b in ($1,$2);
-explain (analyze, costs off, summary off, timing off)  execute q1 (1,1);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (1,1);
                          QUERY PLAN                          
 -------------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -3209,7 +3209,7 @@ explain (analyze, costs off, summary off, timing off)  execute q1 (1,1);
          Filter: (b = ANY (ARRAY[$1, $2]))
 (4 rows)
 
-explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (2,2);
                          QUERY PLAN                          
 -------------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -3219,7 +3219,7 @@ explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
 (4 rows)
 
 -- Try with no matching partitions.
-explain (analyze, costs off, summary off, timing off)  execute q1 (0,0);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (0,0);
            QUERY PLAN           
 --------------------------------
  Append (actual rows=0 loops=1)
@@ -3230,7 +3230,7 @@ deallocate q1;
 -- Test more complex cases where a not-equal condition further eliminates partitions.
 prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
 -- Both partitions allowed by IN clause, but one disallowed by <> clause
-explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (1,2,2,0);
                                QUERY PLAN                                
 -------------------------------------------------------------------------
  Append (actual rows=0 loops=1)
@@ -3240,7 +3240,7 @@ explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
 (4 rows)
 
 -- Both partitions allowed by IN clause, then both excluded again by <> clauses.
-explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (1,2,2,1);
            QUERY PLAN           
 --------------------------------
  Append (actual rows=0 loops=1)
@@ -3248,7 +3248,7 @@ explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
 (2 rows)
 
 -- Ensure Params that evaluate to NULL properly prune away all partitions
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from listp where a = (select null::int);
                       QUERY PLAN                      
 ------------------------------------------------------
@@ -3273,7 +3273,7 @@ create table stable_qual_pruning2 partition of stable_qual_pruning
 create table stable_qual_pruning3 partition of stable_qual_pruning
   for values from ('3000-02-01') to ('3000-03-01');
 -- comparison against a stable value requires run-time pruning
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning where a < localtimestamp;
                                       QUERY PLAN                                      
 --------------------------------------------------------------------------------------
@@ -3286,7 +3286,7 @@ select * from stable_qual_pruning where a < localtimestamp;
 (6 rows)
 
 -- timestamp < timestamptz comparison is only stable, not immutable
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
                                       QUERY PLAN                                      
 --------------------------------------------------------------------------------------
@@ -3297,7 +3297,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
 (4 rows)
 
 -- check ScalarArrayOp cases
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
            QUERY PLAN           
@@ -3306,7 +3306,7 @@ select * from stable_qual_pruning
    One-Time Filter: false
 (2 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
                                                    QUERY PLAN                                                   
@@ -3315,7 +3315,7 @@ select * from stable_qual_pruning
    Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[]))
 (2 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
                                                  QUERY PLAN                                                 
@@ -3326,7 +3326,7 @@ select * from stable_qual_pruning
          Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP]))
 (4 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
            QUERY PLAN           
@@ -3335,7 +3335,7 @@ select * from stable_qual_pruning
    Subplans Removed: 3
 (2 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
                                                         QUERY PLAN                                                         
@@ -3346,7 +3346,7 @@ select * from stable_qual_pruning
          Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[]))
 (4 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(null::timestamptz[]);
                                       QUERY PLAN                                      
@@ -3374,7 +3374,7 @@ create table mc3p1 partition of mc3p
 create table mc3p2 partition of mc3p
   for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
 insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from mc3p where a < 3 and abs(b) = 1;
                        QUERY PLAN                       
 --------------------------------------------------------
@@ -3394,7 +3394,7 @@ select * from mc3p where a < 3 and abs(b) = 1;
 --
 prepare ps1 as
   select * from mc3p where a = $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 execute ps1(1);
                          QUERY PLAN                          
 -------------------------------------------------------------
@@ -3409,7 +3409,7 @@ execute ps1(1);
 deallocate ps1;
 prepare ps2 as
   select * from mc3p where a <= $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 execute ps2(1);
                           QUERY PLAN                          
 --------------------------------------------------------------
@@ -3431,7 +3431,7 @@ insert into boolvalues values('t'),('f');
 create table boolp (a bool) partition by list (a);
 create table boolp_t partition of boolp for values in('t');
 create table boolp_f partition of boolp for values in('f');
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from boolp where a = (select value from boolvalues where value);
                         QUERY PLAN                         
 -----------------------------------------------------------
@@ -3446,7 +3446,7 @@ select * from boolp where a = (select value from boolvalues where value);
          Filter: (a = (InitPlan 1).col1)
 (9 rows)
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from boolp where a = (select value from boolvalues where not value);
                         QUERY PLAN                         
 -----------------------------------------------------------
@@ -3475,7 +3475,7 @@ insert into ma_test select x,x from generate_series(0,29) t(x);
 create index on ma_test (b);
 analyze ma_test;
 prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
-explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
+explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15);
                                        QUERY PLAN                                        
 -----------------------------------------------------------------------------------------
  Merge Append (actual rows=2 loops=1)
@@ -3496,7 +3496,7 @@ execute mt_q1(15);
  25
 (2 rows)
 
-explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
+explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25);
                                        QUERY PLAN                                        
 -----------------------------------------------------------------------------------------
  Merge Append (actual rows=1 loops=1)
@@ -3514,7 +3514,7 @@ execute mt_q1(25);
 (1 row)
 
 -- Ensure MergeAppend behaves correctly when no subplans match
-explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
+explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35);
               QUERY PLAN              
 --------------------------------------
  Merge Append (actual rows=0 loops=1)
@@ -3542,7 +3542,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35
 
 deallocate mt_q2;
 -- ensure initplan params properly prune partitions
-explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
+explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
                                           QUERY PLAN                                           
 -----------------------------------------------------------------------------------------------
  Merge Append (actual rows=20 loops=1)
@@ -3992,7 +3992,7 @@ create table listp (a int, b int) partition by list (a);
 create table listp1 partition of listp for values in(1);
 create table listp2 partition of listp for values in(2) partition by list(b);
 create table listp2_10 partition of listp2 for values in (10);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from listp where a = (select 2) and b <> 10;
                     QUERY PLAN                     
 ---------------------------------------------------
@@ -4117,7 +4117,7 @@ create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3);
 create table rangep_100_to_200 partition of rangep for values from (100) to (200);
 create index on rangep (a);
 -- Ensure run-time pruning works on the nested Merge Append
-explain (analyze on, costs off, timing off, summary off)
+explain (analyze on, costs off, timing off, summary off, buffers off)
 select * from rangep where b IN((select 1),(select 2)) order by a;
                                                  QUERY PLAN                                                 
 ------------------------------------------------------------------------------------------------------------
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index 33a6dceb0e..88911ca2b9 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -757,7 +757,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 (1 row)
 
 -- actually run the query with an analyze to use the partial index
-explain (costs off, analyze on, timing off, summary off)
+explain (costs off, analyze on, timing off, summary off, buffers off)
 select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
                            QUERY PLAN                            
 -----------------------------------------------------------------
diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out
index b79fe9a1c0..d6578d992c 100644
--- a/src/test/regress/expected/select_into.out
+++ b/src/test/regress/expected/select_into.out
@@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a)
   AS SELECT generate_series(1,3) WITH DATA;
 INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
 ERROR:  permission denied for table tbl_withdata1
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
   CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
   SELECT generate_series(1,3) WITH DATA;
               QUERY PLAN              
@@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
 -- EXECUTE and WITH NO DATA, passes.
 CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
   EXECUTE data_sel WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
   CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
   EXECUTE data_sel WITH NO DATA;
           QUERY PLAN           
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 8c31f6460d..a809036453 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -580,7 +580,7 @@ select count(*) from bmscantest where a>1;
 -- test accumulation of stats for parallel nodes
 reset enable_seqscan;
 alter table tenk2 set (parallel_workers = 0);
-explain (analyze, timing off, summary off, costs off)
+explain (analyze, timing off, summary off, costs off, buffers off)
    select count(*) from tenk1, tenk2 where tenk1.hundred > 1
         and tenk2.thousand=0;
                                 QUERY PLAN                                
@@ -606,7 +606,7 @@ $$
 declare ln text;
 begin
     for ln in
-        explain (analyze, timing off, summary off, costs off)
+        explain (analyze, timing off, summary off, costs off, buffers off)
           select * from
           (select ten from tenk1 where ten < 100 order by ten) ss
           right join (values (1),(2),(3)) v(x) on true
@@ -1169,7 +1169,7 @@ explain (costs off)
 -- to increase the parallel query test coverage
 SAVEPOINT settings;
 SET LOCAL debug_parallel_query = 1;
-EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
+EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1;
                          QUERY PLAN                          
 -------------------------------------------------------------
  Gather (actual rows=10000 loops=1)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 2d35de3fad..e04cea3d36 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1675,7 +1675,7 @@ $$
 declare ln text;
 begin
     for ln in
-        explain (analyze, summary off, timing off, costs off)
+        explain (analyze, summary off, timing off, costs off, buffers off)
         select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
     loop
         ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index f133b5a4ac..f6ebdf0601 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -189,7 +189,7 @@ FETCH NEXT FROM c;
 (1 row)
 
 -- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
                     QUERY PLAN                     
 ---------------------------------------------------
@@ -205,7 +205,7 @@ FETCH NEXT FROM c;
 (1 row)
 
 -- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
                     QUERY PLAN                     
 ---------------------------------------------------
@@ -229,7 +229,7 @@ FETCH NEXT FROM c;
 (0 rows)
 
 -- should error out
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
 ERROR:  cursor "c" is not positioned on a row
 ROLLBACK;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 55349b4e1f..dda9fb73e0 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -619,7 +619,7 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_
 SET enable_seqscan = off;
 
 -- make sure the ranges were built correctly and 2023-01-01 eliminates all
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
 
 DROP TABLE brin_date_test;
@@ -636,10 +636,10 @@ CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WI
 
 SET enable_seqscan = off;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
 
 DROP TABLE brin_timestamp_test;
@@ -655,10 +655,10 @@ CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_
 
 SET enable_seqscan = off;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
 
 DROP TABLE brin_date_test;
@@ -676,10 +676,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH
 
 SET enable_seqscan = off;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
 
 DROP TABLE brin_interval_test;
@@ -695,10 +695,10 @@ CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH
 
 SET enable_seqscan = off;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
 
-EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF, BUFFERS OFF)
 SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
 
 DROP TABLE brin_interval_test;
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 3ca285a1d7..3f41396886 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -64,9 +64,10 @@ set track_io_timing = off;
 select explain_filter('explain select * from int8_tbl i8');
 select explain_filter('explain (analyze) select * from int8_tbl i8');
 select explain_filter('explain (analyze, verbose) select * from int8_tbl i8');
-select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8');
-select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8');
-select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8');
+select explain_filter('explain (analyze, format text) select * from int8_tbl i8');
+select explain_filter('explain (analyze, format xml) select * from int8_tbl i8');
+select explain_filter('explain (analyze, buffers off, format text) select * from int8_tbl i8');
+select explain_filter('explain (analyze, serialize, format yaml) select * from int8_tbl i8');
 select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
 select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
 
diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql
index 98b20e17e1..f1f8fae565 100644
--- a/src/test/regress/sql/incremental_sort.sql
+++ b/src/test/regress/sql/incremental_sort.sql
@@ -21,7 +21,7 @@ declare
   line text;
 begin
   for line in
-    execute 'explain (analyze, costs off, summary off, timing off) ' || query
+    execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query
   loop
     out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g');
     return next;
@@ -38,7 +38,7 @@ declare
   element jsonb;
   matching_nodes jsonb := '[]'::jsonb;
 begin
-  execute 'explain (analyze, costs off, summary off, timing off, format ''json'') ' || query into strict elements;
+  execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements;
   while jsonb_array_length(elements) > 0 loop
     element := elements->0;
     elements := elements - 0;
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index 2eaeb1477a..d5aab4e566 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -11,7 +11,7 @@ declare
     ln text;
 begin
     for ln in
-        execute format('explain (analyze, costs off, summary off, timing off) %s',
+        execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
             query)
     loop
         if hide_hitmiss = true then
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
index 5ddcca84f8..54929a92fa 100644
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -1072,7 +1072,7 @@ $$
 DECLARE ln text;
 BEGIN
     FOR ln IN
-        EXECUTE 'explain (analyze, timing off, summary off, costs off) ' ||
+        EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' ||
 		  query
     LOOP
         ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*',  '\1: xxx', 'g');
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 442428d937..8cd4a21fdc 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -12,7 +12,7 @@ declare
     ln text;
 begin
     for ln in
-        execute format('explain (analyze, costs off, summary off, timing off) %s',
+        execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
             query)
     loop
         ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N');
@@ -465,8 +465,8 @@ set enable_indexonlyscan = off;
 prepare ab_q1 (int, int, int) as
 select * from ab where a between $1 and $2 and b <= $3;
 
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3);
 
 deallocate ab_q1;
 
@@ -474,21 +474,21 @@ deallocate ab_q1;
 prepare ab_q1 (int, int) as
 select a from ab where a between $1 and $2 and b < 3;
 
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4);
 
 -- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at
 -- different levels of partitioning.
 prepare ab_q2 (int, int) as
 select a from ab where a between $1 and $2 and b < (select 3);
 
-explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2);
 
 -- As above, but swap the PARAM_EXEC Param to the first partition level
 prepare ab_q3 (int, int) as
 select a from ab where b between $1 and $2 and a < (select 3);
 
-explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2);
 
 --
 -- Test runtime pruning with hash partitioned tables
@@ -538,13 +538,13 @@ begin;
 create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
 
 -- Ensure pruning works using a stable function containing no Vars
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
+explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1);
 
 -- Ensure pruning does not take place when the function has a Var parameter
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
+explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a);
 
 -- Ensure pruning does not take place when the expression contains a Var.
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
+explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a;
 
 rollback;
 
@@ -567,7 +567,7 @@ declare
     ln text;
 begin
     for ln in
-        execute format('explain (analyze, costs off, summary off, timing off) %s',
+        execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
             $1)
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
@@ -650,15 +650,15 @@ reset min_parallel_table_scan_size;
 reset max_parallel_workers_per_gather;
 
 -- Test run-time partition pruning with an initplan
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
 
 -- Test run-time partition pruning with UNION ALL parents
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
 
 -- A case containing a UNION ALL with a non-partitioned child.
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
 
 -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
@@ -678,7 +678,7 @@ union all
 ) ab where a = $1 and b = (select -10);
 
 -- Ensure the xy_1 subplan is not pruned.
-explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
+explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1);
 
 -- Ensure we see just the xy_1 row.
 execute ab_q6(100);
@@ -733,10 +733,10 @@ insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
 set enable_hashjoin = off;
 set enable_mergejoin = off;
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
 
 select tbl1.col1, tprt.col1 from tbl1
@@ -749,10 +749,10 @@ order by tbl1.col1, tprt.col1;
 
 -- Multiple partitions
 insert into tbl1 values (1001), (1010), (1011);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
 
 select tbl1.col1, tprt.col1 from tbl1
@@ -766,7 +766,7 @@ order by tbl1.col1, tprt.col1;
 -- Last partition
 delete from tbl1;
 insert into tbl1 values (4400);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
 
 select tbl1.col1, tprt.col1 from tbl1
@@ -776,7 +776,7 @@ order by tbl1.col1, tprt.col1;
 -- No matching partition
 delete from tbl1;
 insert into tbl1 values (10000);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
 
 select tbl1.col1, tprt.col1 from tbl1
@@ -799,7 +799,7 @@ prepare part_abc_q1 (int, int, int) as
 select * from part_abc where a = $1 and b = $2 and c = $3;
 
 -- Single partition should be scanned.
-explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3);
 
 deallocate part_abc_q1;
 
@@ -819,12 +819,12 @@ select * from listp where b = 1;
 -- which match the given parameter.
 prepare q1 (int,int) as select * from listp where b in ($1,$2);
 
-explain (analyze, costs off, summary off, timing off)  execute q1 (1,1);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (1,1);
 
-explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (2,2);
 
 -- Try with no matching partitions.
-explain (analyze, costs off, summary off, timing off)  execute q1 (0,0);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (0,0);
 
 deallocate q1;
 
@@ -832,13 +832,13 @@ deallocate q1;
 prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
 
 -- Both partitions allowed by IN clause, but one disallowed by <> clause
-explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (1,2,2,0);
 
 -- Both partitions allowed by IN clause, then both excluded again by <> clauses.
-explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
+explain (analyze, costs off, summary off, timing off, buffers off)  execute q1 (1,2,2,1);
 
 -- Ensure Params that evaluate to NULL properly prune away all partitions
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from listp where a = (select null::int);
 
 drop table listp;
@@ -855,30 +855,30 @@ create table stable_qual_pruning3 partition of stable_qual_pruning
   for values from ('3000-02-01') to ('3000-03-01');
 
 -- comparison against a stable value requires run-time pruning
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning where a < localtimestamp;
 
 -- timestamp < timestamptz comparison is only stable, not immutable
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
 
 -- check ScalarArrayOp cases
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from stable_qual_pruning
   where a = any(null::timestamptz[]);
 
@@ -898,7 +898,7 @@ create table mc3p2 partition of mc3p
   for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
 insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from mc3p where a < 3 and abs(b) = 1;
 
 --
@@ -908,12 +908,12 @@ select * from mc3p where a < 3 and abs(b) = 1;
 --
 prepare ps1 as
   select * from mc3p where a = $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 execute ps1(1);
 deallocate ps1;
 prepare ps2 as
   select * from mc3p where a <= $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 execute ps2(1);
 deallocate ps2;
 
@@ -927,10 +927,10 @@ create table boolp (a bool) partition by list (a);
 create table boolp_t partition of boolp for values in('t');
 create table boolp_f partition of boolp for values in('f');
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from boolp where a = (select value from boolvalues where value);
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from boolp where a = (select value from boolvalues where not value);
 
 drop table boolp;
@@ -950,12 +950,12 @@ create index on ma_test (b);
 analyze ma_test;
 prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
 
-explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
+explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15);
 execute mt_q1(15);
-explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
+explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25);
 execute mt_q1(25);
 -- Ensure MergeAppend behaves correctly when no subplans match
-explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
+explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35);
 execute mt_q1(35);
 
 deallocate mt_q1;
@@ -968,7 +968,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35
 deallocate mt_q2;
 
 -- ensure initplan params properly prune partitions
-explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
+explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
 
 reset enable_seqscan;
 reset enable_sort;
@@ -1148,7 +1148,7 @@ create table listp1 partition of listp for values in(1);
 create table listp2 partition of listp for values in(2) partition by list(b);
 create table listp2_10 partition of listp2 for values in (10);
 
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, costs off, summary off, timing off, buffers off)
 select * from listp where a = (select 2) and b <> 10;
 
 --
@@ -1216,7 +1216,7 @@ create table rangep_100_to_200 partition of rangep for values from (100) to (200
 create index on rangep (a);
 
 -- Ensure run-time pruning works on the nested Merge Append
-explain (analyze on, costs off, timing off, summary off)
+explain (analyze on, costs off, timing off, summary off, buffers off)
 select * from rangep where b IN((select 1),(select 2)) order by a;
 reset enable_sort;
 drop table rangep;
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index 019f1e7673..1d1bf2b931 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -196,7 +196,7 @@ explain (costs off)
 select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 -- actually run the query with an analyze to use the partial index
-explain (costs off, analyze on, timing off, summary off)
+explain (costs off, analyze on, timing off, summary off, buffers off)
 select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 explain (costs off)
 select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql
index 689c448cc2..106cdde187 100644
--- a/src/test/regress/sql/select_into.sql
+++ b/src/test/regress/sql/select_into.sql
@@ -30,7 +30,7 @@ SET SESSION AUTHORIZATION regress_selinto_user;
 CREATE TABLE selinto_schema.tbl_withdata1 (a)
   AS SELECT generate_series(1,3) WITH DATA;
 INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
   CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
   SELECT generate_series(1,3) WITH DATA;
 -- WITH NO DATA, passes.
@@ -49,7 +49,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
 -- EXECUTE and WITH NO DATA, passes.
 CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
   EXECUTE data_sel WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
   CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
   EXECUTE data_sel WITH NO DATA;
 RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 5b4a6e1088..71a75bc86e 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -230,7 +230,7 @@ select count(*) from bmscantest where a>1;
 -- test accumulation of stats for parallel nodes
 reset enable_seqscan;
 alter table tenk2 set (parallel_workers = 0);
-explain (analyze, timing off, summary off, costs off)
+explain (analyze, timing off, summary off, costs off, buffers off)
    select count(*) from tenk1, tenk2 where tenk1.hundred > 1
         and tenk2.thousand=0;
 alter table tenk2 reset (parallel_workers);
@@ -242,7 +242,7 @@ $$
 declare ln text;
 begin
     for ln in
-        explain (analyze, timing off, summary off, costs off)
+        explain (analyze, timing off, summary off, costs off, buffers off)
           select * from
           (select ten from tenk1 where ten < 100 order by ten) ss
           right join (values (1),(2),(3)) v(x) on true
@@ -450,7 +450,7 @@ explain (costs off)
 -- to increase the parallel query test coverage
 SAVEPOINT settings;
 SET LOCAL debug_parallel_query = 1;
-EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
+EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1;
 ROLLBACK TO SAVEPOINT settings;
 
 -- provoke error in worker
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index af6e157aca..c53c7f724c 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -857,7 +857,7 @@ $$
 declare ln text;
 begin
     for ln in
-        explain (analyze, summary off, timing off, costs off)
+        explain (analyze, summary off, timing off, costs off, buffers off)
         select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
     loop
         ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b6..1b82d5f1a5 100644
--- a/src/test/regress/sql/tidscan.sql
+++ b/src/test/regress/sql/tidscan.sql
@@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan;
 FETCH NEXT FROM c; -- skip one row
 FETCH NEXT FROM c;
 -- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
 FETCH NEXT FROM c;
 -- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
 SELECT * FROM tidscan;
 -- position cursor past any rows
 FETCH NEXT FROM c;
 -- should error out
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
 ROLLBACK;
 
-- 
2.47.0

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 20 Nov 2024 10:50:36 -0500

On Wed, Nov 20, 2024 at 1:26 AM Guillaume Lelarge <[email-masked]>
wrote:

> OK, but I'm not sure which example I should pick to add dirtied and
> written shared buffers. It looks kinda artificial. Should I choose one
> randomly?
>

It will be artificial, but I think that's ok: anyone running it on their
own will be getting different numbers anyway. I was looking at the "14.1.2
EXPLAIN ANALYZE" section in perform.sgml. Here's some actual numbers I got
with some playing around with concurrent updates:

         Recheck Cond: (unique1 < 10)
>          Heap Blocks: exact=5
>          Buffers: shared hit=2 read=5 written=4

...

>  Planning:
>    Buffers: shared hit=289 dirtied=9

Cheers,
Greg

---

From: Guillaume Lelarge <[email-masked]>
Date: Wed, 20 Nov 2024 20:30:08 +0100

Le mer. 20 nov. 2024 à 16:51, Greg Sabino Mullane <[email-masked]> a

>>    Buffers: shared hit=289 dirtied=9
OK, I'm fine with this. v4 patch attached with one plan showing read,
written, and dirtied buffers.

Thanks for all your comments/reviews.

-- 
Guillaume.
From 484779613694f777e2ffb815a16aa11269404c20 Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge <[email-masked]>
Date: Tue, 12 Nov 2024 21:59:14 +0100
Subject: [PATCH v4] Enable BUFFERS by default with EXPLAIN ANALYZE

index cd12b9ce48..24916b82b5 100644
+         Buffers: shared hit=3 read=5 written=4
+   Buffers: shared hit=15 dirtied=9
 
    <para>
 
 <screen>
 
                                                            QUERY PLAN
 
    <para>

 

 

 

 

 

 
 -- Multiple partitions

 

 

 

 

 
 
 deallocate mt_q2;

 -- perform update
 
 -- perform update
 
 -- should error out

 

 

 

 

 

 

 
 deallocate ab_q1;

 

 

 

 rollback;

 

 

 

 -- Multiple partitions

 

 
 deallocate part_abc_q1;

 

 deallocate q1;

 

 drop table listp;

 

 

 
 
 drop table boolp;

 deallocate mt_q1;

 reset enable_seqscan;
 reset enable_sort;

 

-- 
2.47.0

---

From: David Rowley <[email-masked]>
Date: Thu, 21 Nov 2024 10:22:54 +1300

On Thu, 21 Nov 2024 at 08:30, Guillaume Lelarge <[email-masked]> wrote:
> OK, I'm fine with this. v4 patch attached with one plan showing read, written, and dirtied buffers.

I think this might be a good time for anyone out there who is against
turning on BUFFERS when ANALYZE is on to speak up.

Votes for changing this so far seem to be: Me, Michael Christofides,
Guillaume Lelarge, Robert, Greg Sabino Mullane, and David Fetter (from
2020) [1].

Votes against: None

David

[1] https://www.postgresql.org/message-id/20200601010025.GS13741%40fetter.org

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 20 Nov 2024 16:35:19 -0500

> Guillaume Lelarge, Robert, Greg Sabino Mullane, and David Fetter (from

Please add me to the above list.  What we have now is too confusing.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  When a patient asks the doctor, "Am I going to die?", he means 
  "Am I going to die soon?"

---

From: Vik Fearing <[email-masked]>
Date: Wed, 20 Nov 2024 22:54:23 +0100

> Guillaume Lelarge, Robert, Greg Sabino Mullane, and David Fetter (from

Add me to the pro list, please.

https://www.postgresql.org/message-id/[email-masked]

-- 

Vik Fearing

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 21 Nov 2024 06:13:10 +0100

> https://www.postgresql.org/message-id/[email-masked]

Me, too!
https://postgr.es/m/[email-masked]

Yours,
Laurenz Albe

---

From: Robert Haas <[email-masked]>
Date: Mon, 25 Nov 2024 15:44:38 -0500

> Guillaume Lelarge, Robert, Greg Sabino Mullane, and David Fetter (from

Just to be clear, my vote is more like +0 than +1. I recognize that
changing this is popular and I'm not opposed to it, but I'm also not
unhappy with things as they are.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: David Rowley <[email-masked]>
Date: Tue, 26 Nov 2024 11:52:34 +1300

> unhappy with things as they are.

OK, thanks. I likely took your neutrality here in a more positive
light after reading the 4-year-old message at [1]. That probably
wasn't the right thing to do.

There are still no votes against it, so unless some come in, I plan to
start looking at the patches proposed to turn buffers on with analyze
with my committer hat on.

David

[1] https://www.postgresql.org/message-id/CA%2BTgmoYH_p-y%3D45SAJ58cU6jsMH6ojgqQZiA2aePpvZ0J%2BuLbA%40mail.gmail.com

---

From: Tom Lane <[email-masked]>
Date: Mon, 25 Nov 2024 18:12:33 -0500

> with my committer hat on.

I'm kind of -0.5, but I'd not bothered to vote because it was
pretty clear what the result was going to be.

			regards, tom lane

---

From: David Rowley <[email-masked]>
Date: Wed, 27 Nov 2024 16:02:11 +1300

> pretty clear what the result was going to be.

Just so I can properly understand, how much of the -0.5 is "don't like
the idea" vs "rather make changes to well-known behaviour"?

David

---

From: Daniel Gustafsson <[email-masked]>
Date: Wed, 27 Nov 2024 10:16:45 +0100

> On 20 Nov 2024, at 22:22, David Rowley <[email-masked]> wrote:
> 

I would also like to see this on by default, while it does make it more verbose
the extra data is valuable in debugging. +1 from me.

--
Daniel Gustafsson

---

From: Robert Haas <[email-masked]>
Date: Wed, 27 Nov 2024 11:18:17 -0500

> wasn't the right thing to do.

Yeah, I think my view has wavered over time. Probably when I wrote
that message I had recently seen some cases where the additional
detail would have been helpful; but as of this writing the cases I've
seen most recently are those where the output is mind-numbingly long
already.

---

From: Michael Christofides <[email-masked]>
Date: Mon, 2 Dec 2024 11:41:48 +0000

> v4 patch attached

Thank you Guillaume, and nice work! I tried to see if there was anywhere
else in the documentation that would need updating, but it looks like you
covered everywhere already.

> I'm with Robert in that I've not found the buffer counts to be all that
useful most of the time.

I find the buffer counts especially helpful for educating newer folks on
why things are slow, even when they are not necessary for spotting the
issue (for more advanced users). One of my hopes is that by educating and
empowering newer users on how I/O relates to performance issues, fewer
cases will get escalated to more experienced folks.

> the cases I've seen most recently are those where the output is
mind-numbingly long already.

Are you mostly seeing query plans that have stumped other people already
(eg second or third line support), so perhaps seeing more complex plans
than the average user?

Both Depesz[1] and Tensor[2] have archives of publicly submitted plans,
which I found helpful for checking how slow plans look for users of those
tools. I have a similar archive, and while we do not publish them (and
there are plenty of huge plans) it also suggests that the majority of slow
plans people are reviewing have fewer than 20 nodes.

I realise it’s optimistic to think that the time experienced hackers would
lose having to sift through longer plans would be gained back by having to
do so less often, but I thought it was worth raising as part of the aim.

I also looked into the Slow Query Questions page on the wiki that we ask
people to review before posting to pgsql-performance, and noticed that has
suggested requesting buffers for the past 12 years[3].

—
Michael

[1]: https://explain.depesz.com/history
[2]: https://explain.tensor.ru/archive
[3]:
https://wiki.postgresql.org/index.php?title=Slow_Query_Questions&diff=18308&oldid=16800

---

From: Robert Haas <[email-masked]>
Date: Tue, 3 Dec 2024 09:32:06 -0500

On Mon, Dec 2, 2024 at 6:42 AM Michael Christofides
<[email-masked]> wrote:
> Are you mostly seeing query plans that have stumped other people already (eg second or third line support), so perhaps seeing more complex plans than the average user?

Yes. :-)

---

From: David Rowley <[email-masked]>
Date: Tue, 10 Dec 2024 15:57:43 +1300

> OK, I'm fine with this. v4 patch attached with one plan showing read, written, and dirtied buffers.

Today I spent more time polishing this patch. There were a few cases
in the docs that displayed EXPLAIN ANALYZE output that you'd not
adjusted to include the buffers output or adjusted to do BUFFERS OFF.
I think I've got all these now. Tom went to some effort to fix some
outdated EXPLAIN outputs for v17 in 5caa05749, so I think we owe it to
him not to let these go out of date so soon after that change.

I also was thinking again about what Robert mentioned about
auto_explain.log_buffers should now also be on by default.  I'm less
certain than him about this change. It seems like a separate
consideration that we could apply many of the same arguments for the
main change to.  In any case, I extracted that change from the 0001
patch and put it in a 0002 patch as it doesn't seem like something
that should be a sidenote in the commit message. I felt doing that
increases the chances that it would be overlooked in the release
notes.

I was very close to pushing 0001 today, but will hold off until
tomorrow to see if anyone has final comments.

For 0002, I'd really like to see a bit more justification for it.  For
the record, I'm not against 0002, it's just that my personal arguments
for wanting 0001 don't apply to 0002.

David

---

From: Guillaume Lelarge <[email-masked]>
Date: Tue, 10 Dec 2024 13:22:38 +0100

Hello,

> him not to let these go out of date so soon after that change.
You're right and I completely forgot to check the whole documentation. I
just looked at perform.sgml which was the obvious file for explain plans.
Anyway, sorry about this, and thanks a lot for your work on this patch.

> tomorrow to see if anyone has final comments.
No more comments. I'm fine with both patches.

> for wanting 0001 don't apply to 0002.
I guess consistency is the key word here. But I agree that 0001 is the one
that's really important to me.

Thanks again for your work on this.

-- 
Guillaume.

---

From: David Rowley <[email-masked]>
Date: Wed, 11 Dec 2024 22:38:14 +1300

> No more comments. I'm fine with both patches.

Thanks for looking.

I've pushed the main patch. I'm not planning on pushing the
auto_explain.log_buffers default change unless there's a bit more
discussion about it.

David

---

From: David Rowley <[email-masked]>
Date: Thu, 12 Dec 2024 00:16:15 +1300

> auto_explain.log_buffers default change unless there's a bit more

There were a few places that were missing a BUFFERS OFF as indicated
by the buildfarm. I've fixed a few of these, but there's at least one
more in pg_stat_statements's level_tracking.sql. I'm currently running
a -DRELCACHE_FORCE_RELEASE -DCATCACHE_FORCE_RELEASE enabled make
check-world.  It's going to take a while, so I'll hold off doing yet
another commit to fix these until I see if the level_tracking.sql one
is the last issue to fix.

David

---

From: Guillaume Lelarge <[email-masked]>
Date: Wed, 11 Dec 2024 13:41:09 +0100

> is the last issue to fix.
Sorry for all those late fixes. I'll refrain from saying "Sure looks easy
enough to do" from now on :) Guess this is a great example of what Robert
said in
http://rhaas.blogspot.com/2024/05/hacking-on-postgresql-is-really-hard.html
.

Anyway, thanks again for your work on this.

-- 
Guillaume.

---

From: jian he <[email-masked]>
Date: Wed, 11 Dec 2024 22:00:16 +0800

do we also need to update doc/src/sgml/rules.sgml?

https://www.postgresql.org/docs/current/rules-materializedviews.html

---

From: Guillaume Lelarge <[email-masked]>
Date: Wed, 11 Dec 2024 21:29:36 +0100

Le mer. 11 déc. 2024 à 15:00, jian he <[email-masked]> a

> https://www.postgresql.org/docs/current/rules-materializedviews.html

Yes, you're right. Here is a fix. (Not a patch with a commit message as I
don't know if David will want to commit everything (the CI issue, this, and
probably more) in one patch.)

-- 
Guillaume.
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 7a928bd7b9..6cebed5c37 100644
--- a/doc/src/sgml/rules.sgml
+++ b/doc/src/sgml/rules.sgml
@@ -1042,12 +1042,16 @@ SELECT count(*) FROM words WHERE word = 'caterpiler';
     If the materialized view is used instead, the query is much faster:
 
 <programlisting>
- Aggregate  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
-   -&gt;  Index Only Scan using wrd_word on wrd  (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
+ Aggregate  (cost=4.44..4.45 rows=1 width=8) (actual time=0.138..0.139 rows=1 loops=1)
+   Buffers: shared read=3
+   -&gt;  Index Only Scan using wrd_word on wrd  (cost=0.42..4.44 rows=1 width=0) (actual time=0.132..0.133 rows=0 loops=1)
          Index Cond: (word = 'caterpiler'::text)
          Heap Fetches: 0
- Planning time: 0.164 ms
- Execution time: 0.117 ms
+         Buffers: shared read=3
+ Planning:
+   Buffers: shared hit=5
+ Planning Time: 0.143 ms
+ Execution Time: 0.200 ms
 </programlisting>
 
     Either way, the word is spelled wrong, so let's look for what we might
@@ -1087,11 +1091,15 @@ SELECT word FROM words ORDER BY word &lt;-&gt; 'caterpiler' LIMIT 10;
     Using the materialized view:
 
 <programlisting>
- Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
-   -&gt;  Index Scan using wrd_trgm on wrd  (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1)
+ Limit  (cost=0.29..1.06 rows=10 width=14) (actual time=135.027..135.475 rows=10 loops=1)
+   Buffers: shared hit=4465
+   -&gt;  Index Scan using wrd_trgm on wrd  (cost=0.29..37224.81 rows=479826 width=14) (actual time=135.023..135.470 rows=10 loops=1)
          Order By: (word &lt;-&gt; 'caterpiler'::text)
- Planning time: 0.196 ms
- Execution time: 198.640 ms
+         Buffers: shared hit=4465
+ Planning:
+   Buffers: shared hit=80
+ Planning Time: 0.767 ms
+ Execution Time: 140.836 ms
 </programlisting>
 
     If you can tolerate periodic update of the remote data to the local

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Wed, 11 Dec 2024 21:41:33 +0100

> auto_explain.log_buffers default change unless there's a bit more

FreeBSD CFBot seems broken on a pg_stat_statements test because of
this change: https://api.cirrus-ci.com/v1/artifact/task/4845908522696704/testrun/build/testrun/pg_stat_statements/regress/regression.diffs

---

From: Guillaume Lelarge <[email-masked]>
Date: Wed, 11 Dec 2024 21:46:16 +0100

Le mer. 11 déc. 2024 à 21:41, Jelte Fennema-Nio <[email-masked]> a
écrit :

> this change:
> https://api.cirrus-ci.com/v1/artifact/task/4845908522696704/testrun/build/testrun/pg_stat_statements/regress/regression.diffs
>

Thanks for the info. Peter sent an email on the pgsql-committers list to
say the same thing. I wrote a patch for this, and sent it to the same list.
See
https://www.postgresql.org/message-id/CAECtzeVXyRruRODFXZ67goMOYtieLbLmcUY6P5L_LrEq8b%3DgxQ%40mail.gmail.com
for more details.

-- 
Guillaume.

---

From: David Rowley <[email-masked]>
Date: Thu, 12 Dec 2024 09:55:41 +1300

> is the last issue to fix.

That run didn't show any other failures, so I pushed the patch.

David

---

From: Michael Christofides <[email-masked]>
Date: Fri, 13 Dec 2024 15:59:09 +0000

> I've pushed the main patch.

Woohoo! And thank you. I've already seen quite a lot of positivity around
the commit on Twitter[1][2][3].

> I'm not planning on pushing the auto_explain.log_buffers default change
unless there's a bit more discussion about it.

Much like Guillaume, I'd also be in favour of 0002, but it's nowhere near
as important to me. I think most people consider the parameters far more
when setting up auto_explain, so I believe far fewer omit buffers by
mistake. Also, most cloud providers don't ship with auto_explain on, and
the only one I know of that does[4], ships with log_buffers on too. On the
plus side, it would be nice to be consistent. But on the downside, it might
add a little extra overhead for folks who run auto_explain with log_analyze
on, and who opted not to set log_buffers and upgrade without setting it to
off explicitly. I am still in favour of the 0002 patch being applied, to
avoid confusion and maximise the chance people that don't know about
buffers still get them in their plans.

> https://www.postgresql.org/docs/current/rules-materializedviews.html

Good catch. Testing those file_fdw queries locally, I don't see buffers
reported by the Foreign Scan, but I do initially see some Planning buffers
(on first run). The two plans from the queries on the materialized view do
show buffers now though, of course. Since the file_fdw Foreign Scan is not
reporting buffers, I'm wondering if in this one case simply changing "With
EXPLAIN ANALYZE" to "With EXPLAIN (ANALYZE, BUFFERS OFF)" might be the
least confusing solution?

Thanks again all,
Michael

[1]: https://x.com/nori_shinoda/status/1866805465897898319
[2]: https://x.com/samokhvalov/status/1866863468172939457
[3]: https://x.com/mmeent_pg/status/1866796928002044196
[4]:
https://www.pgmustard.com/blog/which-cloud-providers-support-auto-explain

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Sun, 9 Feb 2025 13:05:32 +0100

On Fri, 13 Dec 2024 at 16:59, Michael Christofides
<[email-masked]> wrote:
> Woohoo! And thank you. I've already seen quite a lot of positivity around the commit on Twitter[1][2][3].

I wanted to highlight this other patch[1] by Atsushi Torikoshi, which
adds "actual filesystem I/O" metrics to EXPLAIN BUFFERS (and thus now
to default ANALYZE). I marked that patch as "ready for committer" just
now. I'm sharing it here for visibility, because it seems like people
interested in BUFFERS as default would probably also be interested in
that patch. And because it's pretty simple, I think it would be a good
candidate to commit for PG18.

[1]: https://commitfest.postgresql.org/52/5526/

---

From: Fujii Masao <[email-masked]>
Date: Thu, 29 May 2025 13:28:45 +0900

On 2024/12/14 0:59, Michael Christofides wrote:
>  > I'm not planning on pushing the auto_explain.log_buffers default change unless there's a bit more discussion about it.
> 
> Much like Guillaume, I'd also be in favour of 0002, but it's nowhere near as important to me. I think most people consider the parameters far more when setting up auto_explain, so I believe far fewer omit buffers by mistake. Also, most cloud providers don't ship with auto_explain on, and the only one I know of that does[4], ships with log_buffers on too. On the plus side, it would be nice to be consistent. But on the downside, it might add a little extra overhead for folks who run auto_explain with log_analyze on, and who opted not to set log_buffers and upgrade without setting it to off explicitly. I am still in favour of the 0002 patch being applied, to avoid confusion and maximise the chance people that don't know about buffers still get them in their plans.

I agree with changing the default value of auto_explain.log_buffers to true.
I think that users who know EXPLAIN ANALYZE includes buffers info in
the result by default since v18 would expect the buffer info also to
be included in auto_explain output as long as log_analyze is enabled.
So setting log_buffers to true by default would be less confusing.

As for 0002 patch, should the example log output with log_analyze enabled
also be updated to show the buffer information?

Sorry for reviving this old thread, but while testing the new v18 feature
- buffers being shown by default - I had the same thought about
auto_explain.log_buffers, so I wanted to chime in.

Regards,

-- 
Fujii Masao
NTT DATA Japan Corporation

---

From: David Rowley <[email-masked]>
Date: Fri, 30 May 2025 23:55:20 +1200

> So setting log_buffers to true by default would be less confusing.

I feel somewhat responsible to answer here since I committed the
EXPLAIN ANALYZE change. I'm still neutral.

By my count, those who think we should change log_buffers to true are:
Robert [1], Michael Christofides [2], Fujii [3].

I expect arguments against might be related to log space or
performance. I did a quick test to try to get an idea of what we're
looking at. I'm unsure how realistic it would be that someone would
use auto_explain to log this many plans, but there is a bit of a
performance overhead to enabling log_buffers. Full results attached,
but here's the summary after ignoring the first result of each and
taking the average of the remaining 9 runs of each. I deleted the log
file between each test.

log_min_duration = 0, log_buffers = off: 11231 tps
log_min_duration = 0, log_buffers = on: 11106 tps
log_min_duration = -1 (off): 13297 tps

So it is possible to measure it, at least.

David

[1] https://www.postgresql.org/message-id/CA%2BTgmoZO2HtDGHf7K4rmNdfUGuS%3DngsPgwrH%3DJdyF6Okcnzfkw%40mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/a6206e08-0607-42fe-91fd-e7bd45fa235a%40oss.nttdata.com#47cd88d71be28287d3e29c833d98eb48
[3] https://www.postgresql.org/message-id/a6206e08-0607-42fe-91fd-e7bd45fa235a%40oss.nttdata.com

---

From: "Jelte Fennema-Nio" <[email-masked]>
Date: Fri, 30 May 2025 14:43:14 +0200

> Robert [1], Michael Christofides [2], Fujii [3].

+1 for me on changing the default. It just seems confusing to have
different defaults for auto_explain and EXPLAIN. I'd even vote for
making that change still for PG18, since this feels like an oversight
that the defaults were not kept aligned.

> So it is possible to measure it, at least.

Seems small enough not to worry about it.

---

From: Fujii Masao <[email-masked]>
Date: Fri, 30 May 2025 23:36:40 +0900

> Seems small enough not to worry about it.

Users can simply disable log_buffers if they enable log_analyze
but don't need buffer usage details and want to avoid the overhead.
If there were no way to avoid the overhead, I'd agree not to make
this change. But since we can disable log_buffers when needed,
I think it's acceptable.

Regards,