Subject: explain_regress, explain(MACHINE), and default to explain(BUFFERS) (was: BUFFERS enabled by default in EXPLAIN (ANALYZE))

From: Justin Pryzby <[email-masked]>
Date: Mon, 24 Jan 2022 11:35:48 -0600

I'm renaming this thread for better visibility, since buffers is a small,
optional part of the patches I sent.

I made a CF entry here.
https://commitfest.postgresql.org/36/3409/

On Wed, Dec 01, 2021 at 06:58:20PM -0600, Justin Pryzby wrote:
> On Mon, Nov 15, 2021 at 01:09:54PM -0600, Justin Pryzby wrote:
> > Some time ago, I had a few relevant patches:
> > 1) add explain(REGRESS) which is shorthand for (BUFFERS OFF, TIMING OFF, COSTS OFF, SUMMARY OFF)
> > 2) add explain(MACHINE) which elides machine-specific output from explain;
> >    for example, Heap Fetches, sort spaceUsed, hash nbuckets, and tidbitmap stuff.
> > 
> > https://www.postgresql.org/message-id/flat/[email-masked]
> 
> The attached patch series now looks like this (some minor patches are not
> included in this list):
> 
>  1. add GUC explain_regress, which disables TIMING, SUMMARY, COSTS;
>  2. enable explain(BUFFERS) by default (but disabled by explain_regress);
>  3. Add explain(MACHINE) - which is disabled by explain_regress.
>     This elides various machine-specific output like Memory and Disk use.
>     Maybe it should be called something else like "QUIET" or "VERBOSE_MINUS_1"
>     or ??
> 
> The regression tests now automatically run with explain_regress=on, which is
> shorthand for TIMING OFF, SUMMARY OFF, COSTS OFF, and then BUFFERS OFF.
> 
> There's a further option called explain(MACHINE) which can be disabled to hide
> portions of the output that are unstable, like Memory/Disk/Batches/
> Heap Fetches/Heap Blocks.  This allows "explain analyze" to be used more easily
> in regression tests, and simplifies some existing tests that currently use
> plpgsql functions to filter the output.  But it doesn't handle all the
> variations from parallel workers.
> 
> (3) is optional, but simplifies some regression tests.  The patch series could
> be rephrased with (3) first.
> 
> Unfortunately, "COSTS OFF" breaks postgres_fdw remote_estimate.  If specifying
> "COSTS ON" in postgres_fdw.c is considered to be a poor fix , then I suppose
> this patch series could do as suggested and enable buffers by default only when
> ANALYZE is specified.  Then postgres_fdw is not affected, and the
> explain_regress GUC is optional: instead, we'd need to specify BUFFERS OFF in
> ~100 regression tests which use EXPLAIN ANALYZE.  (3) still seems useful on its
> own.
>From 747cb7a979cf96f99403a005053e635f3bf8c3f0 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Sat, 22 Feb 2020 21:17:10 -0600
Subject: [PATCH 1/7] Add GUC: explain_regress

This changes the defaults for explain to: costs off, timing off, summary off.
It'd be reasonable to use this for new regression tests which are not intended
to be backpatched.
---
 contrib/postgres_fdw/postgres_fdw.c     |  2 +-
 src/backend/commands/explain.c          | 13 +++++++++++--
 src/backend/utils/misc/guc.c            | 13 +++++++++++++
 src/include/commands/explain.h          |  2 ++
 src/test/regress/expected/explain.out   |  3 +++
 src/test/regress/expected/inherit.out   |  2 +-
 src/test/regress/expected/stats_ext.out |  2 +-
 src/test/regress/pg_regress.c           |  3 ++-
 src/test/regress/sql/explain.sql        |  4 ++++
 src/test/regress/sql/inherit.sql        |  2 +-
 src/test/regress/sql/stats_ext.sql      |  2 +-
 11 files changed, 40 insertions(+), 8 deletions(-)

diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index bf3f3d9e26e..71f5b145984 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3112,7 +3112,7 @@ estimate_path_cost_size(PlannerInfo *root,
 		 * values, so don't request params_list.
 		 */
 		initStringInfo(&sql);
-		appendStringInfoString(&sql, "EXPLAIN ");
+		appendStringInfoString(&sql, "EXPLAIN (COSTS)");
 		deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist,
 								remote_conds, pathkeys,
 								fpextra ? fpextra->has_final_sort : false,
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index b970997c346..43e8bc78778 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -154,6 +154,7 @@ static void ExplainJSONLineEnding(ExplainState *es);
 static void ExplainYAMLLineStarting(ExplainState *es);
 static void escape_yaml(StringInfo buf, const char *str);
 
+bool explain_regress = false; /* GUC */

 /*
@@ -172,6 +173,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	ListCell   *lc;
 	bool		timing_set = false;
 	bool		summary_set = false;
+	bool		costs_set = false;
 
 	/* Parse options list. */
 	foreach(lc, stmt->options)
@@ -183,7 +185,11 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 		else if (strcmp(opt->defname, "verbose") == 0)
 			es->verbose = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "costs") == 0)
+		{
+			/* Need to keep track if it was explicitly set to ON */
+			costs_set = true;
 			es->costs = defGetBoolean(opt);
+		}
 		else if (strcmp(opt->defname, "buffers") == 0)
 			es->buffers = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "wal") == 0)
@@ -227,13 +233,16 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 					 parser_errposition(pstate, opt->location)));
 	}
 
+	/* if the costs option was not set explicitly, set default value */
+	es->costs = (costs_set) ? es->costs : es->costs && !explain_regress;
+
 	if (es->wal && !es->analyze)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("EXPLAIN option WAL requires ANALYZE")));
 
 	/* if the timing was not set explicitly, set default value */
-	es->timing = (timing_set) ? es->timing : es->analyze;
+	es->timing = (timing_set) ? es->timing : es->analyze && !explain_regress;
 
 	/* check that timing is used with EXPLAIN ANALYZE */
 	if (es->timing && !es->analyze)
@@ -242,7 +251,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 				 errmsg("EXPLAIN option TIMING requires ANALYZE")));
 
 	/* if the summary was not set explicitly, set default value */
-	es->summary = (summary_set) ? es->summary : es->analyze;
+	es->summary = (summary_set) ? es->summary : es->analyze && !explain_regress;
 
 	query = castNode(Query, stmt->query);
 	if (IsQueryIdEnabled())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 4c94f09c645..69f403f0c14 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -45,6 +45,7 @@
 #include "catalog/pg_authid.h"
 #include "catalog/storage.h"
 #include "commands/async.h"
+#include "commands/explain.h"
 #include "commands/prepare.h"
 #include "commands/tablespace.h"
 #include "commands/trigger.h"
@@ -1470,6 +1471,18 @@ static struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+
+	{
+		{"explain_regress", PGC_USERSET, DEVELOPER_OPTIONS,
+			gettext_noop("Change defaults of EXPLAIN to avoid unstable output."),
+			NULL,
+			GUC_NOT_IN_SAMPLE | GUC_EXPLAIN
+		},
+		&explain_regress,
+		false,
+		NULL, NULL, NULL
+	},
+
 	{
 		{"log_parser_stats", PGC_SUSET, STATS_MONITORING,
 			gettext_noop("Writes parser performance statistics to the server log."),
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 666977fb1f8..ce8c458d731 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -61,6 +61,8 @@ typedef struct ExplainState
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
 } ExplainState;
 
+extern bool explain_regress;
+
 /* Hook for plugins to get control in ExplainOneQuery() */
 typedef void (*ExplainOneQuery_hook_type) (Query *query,
 										   int cursorOptions,
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 1734dfee8cc..188dc7ccec9 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -8,6 +8,9 @@
 -- To produce stable regression test output, it's usually necessary to
 -- ignore details such as exact costs or row counts.  These filter
 -- functions replace changeable output details with fixed strings.
+-- Output normal, user-facing details, not the sanitized version used for the
+-- rest of the regression tests
+set explain_regress = off;
 create function explain_filter(text) returns setof text
 language plpgsql as
 $$
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 2d49e765de8..38fb5f94c6a 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -664,7 +664,7 @@ select tableoid::regclass::text as relname, parted_tab.* from parted_tab order b
 (3 rows)
 
 -- modifies partition key, but no rows will actually be updated
-explain update parted_tab set a = 2 where false;
+explain (costs on) update parted_tab set a = 2 where false;
                        QUERY PLAN                       
 --------------------------------------------------------
  Update on parted_tab  (cost=0.00..0.00 rows=0 width=0)
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 042316aeed8..638abaaf804 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -14,7 +14,7 @@ declare
     first_row bool := true;
 begin
     for ln in
-        execute format('explain analyze %s', $1)
+        execute format('explain (analyze, costs on) %s', $1)
     loop
         if first_row then
             first_row := false;
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index e6f71c7582e..919ede8b768 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -634,7 +634,7 @@ initialize_environment(void)
 	 * user's ability to set other variables through that.
 	 */
 	{
-		const char *my_pgoptions = "-c intervalstyle=postgres_verbose";
+		const char *my_pgoptions = "-c intervalstyle=postgres_verbose -c explain_regress=on";
 		const char *old_pgoptions = getenv("PGOPTIONS");
 		char	   *new_pgoptions;
 
@@ -2296,6 +2296,7 @@ regression_main(int argc, char *argv[],
 		fputs("log_lock_waits = on\n", pg_conf);
 		fputs("log_temp_files = 128kB\n", pg_conf);
 		fputs("max_prepared_transactions = 2\n", pg_conf);
+		// fputs("explain_regress = yes\n", pg_conf);
 
 		for (sl = temp_configs; sl != NULL; sl = sl->next)
 		{
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 84549c78fa8..f91d1004d5e 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -10,6 +10,10 @@
 -- ignore details such as exact costs or row counts.  These filter
 -- functions replace changeable output details with fixed strings.
 
+-- Output normal, user-facing details, not the sanitized version used for the
+-- rest of the regression tests
+set explain_regress = off;
+
 create function explain_filter(text) returns setof text
 language plpgsql as
 $$
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 195aedb5ff5..868ee58b803 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -169,7 +169,7 @@ where parted_tab.a = ss.a;
 select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2;
 
 -- modifies partition key, but no rows will actually be updated
-explain update parted_tab set a = 2 where false;
+explain (costs on) update parted_tab set a = 2 where false;
 
 drop table parted_tab;
 
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 6b954c9e500..4c91f608634 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -16,7 +16,7 @@ declare
     first_row bool := true;
 begin
     for ln in
-        execute format('explain analyze %s', $1)
+        execute format('explain (analyze, costs on) %s', $1)
     loop
         if first_row then
             first_row := false;
-- 
2.17.1

>From 1b5d8a68ecded561bc41b52759896df298faf990 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Mon, 15 Nov 2021 21:54:12 -0600
Subject: [PATCH 2/7] exercise explain_regress

not intended to be merged, since it creates backpatch hazards (unless the GUC
is also backpatched)
---
 src/test/regress/expected/matview.out     | 12 ++++++------
 src/test/regress/expected/select_into.out | 20 ++++++++++----------
 src/test/regress/expected/tidscan.out     |  6 +++---
 src/test/regress/sql/matview.sql          | 12 ++++++------
 src/test/regress/sql/select_into.sql      | 20 ++++++++++----------
 src/test/regress/sql/tidscan.sql          |  6 +++---
 6 files changed, 38 insertions(+), 38 deletions(-)

diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 313c72a2685..e7ce5bb1887 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public;
 SET SESSION AUTHORIZATION regress_matview_user;
 CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
   SELECT generate_series(1, 10) WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
   SELECT generate_series(1, 10) WITH DATA;
               QUERY PLAN              
@@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
 REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
 CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
   SELECT generate_series(1, 10) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
   SELECT generate_series(1, 10) WITH NO DATA;
           QUERY PLAN           
@@ -651,11 +651,11 @@ ERROR:  relation "matview_ine_tab" already exists
 CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
   SELECT 1 / 0 WITH NO DATA; -- ok
 NOTICE:  relation "matview_ine_tab" already exists, skipping
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_ine_tab AS
     SELECT 1 / 0; -- error
 ERROR:  relation "matview_ine_tab" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
     SELECT 1 / 0; -- ok
 NOTICE:  relation "matview_ine_tab" already exists, skipping
@@ -663,11 +663,11 @@ NOTICE:  relation "matview_ine_tab" already exists, skipping
 ------------
 (0 rows)
 
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_ine_tab AS
     SELECT 1 / 0 WITH NO DATA; -- error
 ERROR:  relation "matview_ine_tab" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
     SELECT 1 / 0 WITH NO DATA; -- ok
 NOTICE:  relation "matview_ine_tab" already exists, skipping
diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out
index 43b8209d229..3de0be2a154 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)
   CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
   SELECT generate_series(1,3) WITH DATA;
               QUERY PLAN              
@@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
 -- WITH NO DATA, passes.
 CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
   SELECT generate_series(1,3) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
   SELECT generate_series(1,3) WITH NO DATA;
           QUERY PLAN           
@@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
 PREPARE data_sel AS SELECT generate_series(1,3);
 CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
   EXECUTE data_sel WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
   EXECUTE data_sel 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)
   CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
   EXECUTE data_sel WITH NO DATA;
           QUERY PLAN           
@@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
 ERROR:  relation "ctas_ine_tbl" already exists
 CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
 NOTICE:  relation "ctas_ine_tbl" already exists, skipping
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
 ERROR:  relation "ctas_ine_tbl" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
 NOTICE:  relation "ctas_ine_tbl" already exists, skipping
  QUERY PLAN 
 ------------
 (0 rows)
 
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
 ERROR:  relation "ctas_ine_tbl" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
 NOTICE:  relation "ctas_ine_tbl" already exists, skipping
  QUERY PLAN 
@@ -209,10 +209,10 @@ NOTICE:  relation "ctas_ine_tbl" already exists, skipping
 (0 rows)
 
 PREPARE ctas_ine_query AS SELECT 1 / 0;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
 ERROR:  relation "ctas_ine_tbl" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
 NOTICE:  relation "ctas_ine_tbl" already exists, skipping
  QUERY PLAN 
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index 13c3c360c25..de93145bf0d 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)
 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)
 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)
 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/matview.sql b/src/test/regress/sql/matview.sql
index 68b9ccfd452..e0b562933d0 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public;
 SET SESSION AUTHORIZATION regress_matview_user;
 CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
   SELECT generate_series(1, 10) WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
   SELECT generate_series(1, 10) WITH DATA;
 REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
 CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
   SELECT generate_series(1, 10) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
   SELECT generate_series(1, 10) WITH NO DATA;
 REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
@@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS
   SELECT 1 / 0 WITH NO DATA; -- error
 CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
   SELECT 1 / 0 WITH NO DATA; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_ine_tab AS
     SELECT 1 / 0; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
     SELECT 1 / 0; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW matview_ine_tab AS
     SELECT 1 / 0 WITH NO DATA; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
     SELECT 1 / 0 WITH NO DATA; -- ok
 DROP MATERIALIZED VIEW matview_ine_tab;
diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql
index 7e903c339a8..7031e86a2c9 100644
--- a/src/test/regress/sql/select_into.sql
+++ b/src/test/regress/sql/select_into.sql
@@ -30,26 +30,26 @@ 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)
   CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
   SELECT generate_series(1,3) WITH DATA;
 -- WITH NO DATA, passes.
 CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
   SELECT generate_series(1,3) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
   SELECT generate_series(1,3) WITH NO DATA;
 -- EXECUTE and WITH DATA, passes.
 PREPARE data_sel AS SELECT generate_series(1,3);
 CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
   EXECUTE data_sel WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
   EXECUTE data_sel WITH DATA;
 -- 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)
   CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
   EXECUTE data_sel WITH NO DATA;
 RESET SESSION AUTHORIZATION;
@@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
 CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
 CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
 CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
 PREPARE ctas_ine_query AS SELECT 1 / 0;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
   CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
 DROP TABLE ctas_ine_tbl;
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b67..3d1f447088f 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)
 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)
 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)
 UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
 ROLLBACK;
 
-- 
2.17.1

>From c176127e9da57fb85c93a541f9c110b1551c5b98 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Wed, 22 Jul 2020 19:20:40 -0500
Subject: [PATCH 3/7] Make explain default to BUFFERS TRUE

---
 contrib/auto_explain/auto_explain.c | 4 ++--
 doc/src/sgml/config.sgml            | 2 +-
 doc/src/sgml/perform.sgml           | 4 ++--
 doc/src/sgml/ref/explain.sgml       | 2 +-
 src/backend/commands/explain.c      | 8 ++++++++
 5 files changed, 14 insertions(+), 6 deletions(-)

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 3e09abaecac..1ed43d3c8a8 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -27,7 +27,7 @@ PG_MODULE_MAGIC;
 static int	auto_explain_log_min_duration = -1; /* msec 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;
@@ -143,7 +143,7 @@ _PG_init(void)
 							 "Log buffers usage.",
 							 NULL,
 							 &auto_explain_log_buffers,
-							 false,
+							 true,
 							 PGC_SUSET,
 							 0,
 							 NULL,
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 4cd9818acf8..a99bd58f8ba 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7813,7 +7813,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
         displayed in <link linkend="monitoring-pg-stat-database-view">
         <structname>pg_stat_database</structname></link>, in the output of
         <xref linkend="sql-explain"/> when the <literal>BUFFERS</literal> option
-        is used, in the output of <xref linkend="sql-vacuum"/> when
+        is enabled, in the output of <xref linkend="sql-vacuum"/> when
         the <literal>VERBOSE</literal> option is used, by autovacuum
         for auto-vacuums and auto-analyzes, when <xref
          linkend="guc-log-autovacuum-min-duration"/> is set and by
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e5..fc4367ffe6e 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -731,8 +731,8 @@ 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 ANALYZE</command> has a <literal>BUFFERS</literal> option which
+    provides even more run time statistics:
 
 <screen>
 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 &lt; 100 AND unique2 &gt; 9000;
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 4d758fb237e..ceb0f4c83a1 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -190,7 +190,7 @@ ROLLBACK;
       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.  It defaults to
-      <literal>FALSE</literal>.
+      <literal>TRUE</literal>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 43e8bc78778..6cc04f90fb4 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -174,6 +174,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	bool		timing_set = false;
 	bool		summary_set = false;
 	bool		costs_set = false;
+	bool		buffers_set = false;
 
 	/* Parse options list. */
 	foreach(lc, stmt->options)
@@ -191,7 +192,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 			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)
@@ -253,6 +257,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	/* if the summary was not set explicitly, set default value */
 	es->summary = (summary_set) ? es->summary : es->analyze && !explain_regress;
 
+	/* if the buffers option was not set explicitly, set default value */
+	es->buffers = (buffers_set) ? es->buffers : !explain_regress;
+
 	query = castNode(Query, stmt->query);
 	if (IsQueryIdEnabled())
 		jstate = JumbleQuery(query, pstate->p_sourcetext);
@@ -323,6 +330,7 @@ NewExplainState(void)
 
 	/* Set default options (most fields can be left as zeroes). */
 	es->costs = true;
+	es->buffers = true;
 	/* Prepare output buffer. */
 	es->str = makeStringInfo();
 
-- 
2.17.1

>From a02bd2897add538803a72d20a7fd9030f9b406d3 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Sat, 22 Feb 2020 18:45:22 -0600
Subject: [PATCH 4/7] Add explain(MACHINE) to hide machine-dependent output..

This new option hides some output that has traditionally been shown; the option
is enabled by regression mode to hide unstable output.

This allows EXPLAIN ANALYZE to be used in regression tests with stable output.
This is like a "quiet" mode, or negative verbosity.

Also add regression tests for HashAgg and Bitmap scan, which previously had no
tests with explain(analyze).

This does not handle variations in "Workers Launched", or other parallel worker
bits which are handled by force_parallel_mode=regress.

Also add tests for show_hashagg_info and tidbitmap, for which there's no other
test.
---
 src/backend/commands/explain.c                | 77 +++++++++++++------
 src/include/commands/explain.h                |  1 +
 src/test/isolation/expected/horizons.out      | 40 +++++-----
 src/test/isolation/specs/horizons.spec        |  2 +-
 src/test/regress/expected/explain.out         | 55 +++++++++++++
 .../regress/expected/incremental_sort.out     |  4 +-
 src/test/regress/expected/memoize.out         | 35 ++++-----
 src/test/regress/expected/partition_prune.out |  4 +-
 src/test/regress/expected/select_parallel.out | 32 +++-----
 src/test/regress/expected/subselect.out       | 21 +----
 src/test/regress/sql/explain.sql              | 17 ++++
 src/test/regress/sql/memoize.sql              |  4 +-
 src/test/regress/sql/select_parallel.sql      | 20 +----
 src/test/regress/sql/subselect.sql            | 19 +----
 14 files changed, 182 insertions(+), 149 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 6cc04f90fb4..8b4ff9624d4 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -175,6 +175,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	bool		summary_set = false;
 	bool		costs_set = false;
 	bool		buffers_set = false;
+	bool		machine_set = false;
 
 	/* Parse options list. */
 	foreach(lc, stmt->options)
@@ -210,6 +211,11 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 			summary_set = true;
 			es->summary = defGetBoolean(opt);
 		}
+		else if (strcmp(opt->defname, "machine") == 0)
+		{
+			machine_set = true;
+			es->machine = defGetBoolean(opt);
+		}
 		else if (strcmp(opt->defname, "format") == 0)
 		{
 			char	   *p = defGetString(opt);
@@ -260,6 +266,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
 	/* if the buffers option was not set explicitly, set default value */
 	es->buffers = (buffers_set) ? es->buffers : !explain_regress;
 
+	/* if the machine option was not set explicitly, set default value */
+	es->machine = (machine_set) ? es->machine : !explain_regress;
+
 	query = castNode(Query, stmt->query);
 	if (IsQueryIdEnabled())
 		jstate = JumbleQuery(query, pstate->p_sourcetext);
@@ -621,7 +630,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 	/* Create textual dump of plan tree */
 	ExplainPrintPlan(es, queryDesc);
 
-	if (es->verbose && plannedstmt->queryId != UINT64CONST(0))
+	if (es->verbose && plannedstmt->queryId != UINT64CONST(0) && es->machine)
 	{
 		/*
 		 * Output the queryid as an int64 rather than a uint64 so we match
@@ -632,7 +641,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 	}
 
 	/* Show buffer usage in planning */
-	if (bufusage)
+	if (bufusage && es->buffers)
 	{
 		ExplainOpenGroup("Planning", "Planning", true, es);
 		show_buffer_usage(es, bufusage, true);
@@ -1772,7 +1781,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			if (plan->qual)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
-			if (es->analyze)
+			if (es->analyze && es->machine)
 				ExplainPropertyFloat("Heap Fetches", NULL,
 									 planstate->instrument->ntuples2, 0, es);
 			break;
@@ -2746,8 +2755,12 @@ show_sort_info(SortState *sortstate, ExplainState *es)
 		if (es->format == EXPLAIN_FORMAT_TEXT)
 		{
 			ExplainIndentText(es);
-			appendStringInfo(es->str, "Sort Method: %s  %s: " INT64_FORMAT "kB\n",
-							 sortMethod, spaceType, spaceUsed);
+			appendStringInfo(es->str, "Sort Method: %s",
+							 sortMethod);
+			if (es->machine)
+				appendStringInfo(es->str, "  %s: " INT64_FORMAT "kB",
+							 spaceType, spaceUsed);
+			appendStringInfoString(es->str, "\n");
 		}
 		else
 		{
@@ -2791,8 +2804,12 @@ show_sort_info(SortState *sortstate, ExplainState *es)
 			{
 				ExplainIndentText(es);
 				appendStringInfo(es->str,
-								 "Sort Method: %s  %s: " INT64_FORMAT "kB\n",
-								 sortMethod, spaceType, spaceUsed);
+								 "Sort Method: %s",
+								 sortMethod);
+				if (es->machine)
+					appendStringInfo(es->str, "  %s: " INT64_FORMAT "kB", spaceType, spaceUsed);
+
+				appendStringInfoString(es->str, "\n");
 			}
 			else
 			{
@@ -3080,25 +3097,26 @@ show_hash_info(HashState *hashstate, ExplainState *es)
 			ExplainPropertyInteger("Peak Memory Usage", "kB",
 								   spacePeakKb, es);
 		}
-		else if (hinstrument.nbatch_original != hinstrument.nbatch ||
-				 hinstrument.nbuckets_original != hinstrument.nbuckets)
+		else
 		{
 			ExplainIndentText(es);
-			appendStringInfo(es->str,
-							 "Buckets: %d (originally %d)  Batches: %d (originally %d)  Memory Usage: %ldkB\n",
+			if (hinstrument.nbatch_original != hinstrument.nbatch ||
+				 hinstrument.nbuckets_original != hinstrument.nbuckets)
+				appendStringInfo(es->str,
+							 "Buckets: %d (originally %d)  Batches: %d (originally %d)",
 							 hinstrument.nbuckets,
 							 hinstrument.nbuckets_original,
 							 hinstrument.nbatch,
-							 hinstrument.nbatch_original,
-							 spacePeakKb);
-		}
-		else
-		{
-			ExplainIndentText(es);
-			appendStringInfo(es->str,
-							 "Buckets: %d  Batches: %d  Memory Usage: %ldkB\n",
-							 hinstrument.nbuckets, hinstrument.nbatch,
-							 spacePeakKb);
+							 hinstrument.nbatch_original);
+			else
+				appendStringInfo(es->str,
+							 "Buckets: %d  Batches: %d",
+							 hinstrument.nbuckets, hinstrument.nbatch);
+
+			if (es->machine)
+				appendStringInfo(es->str, "  Memory Usage: %ldkB", spacePeakKb);
+
+			appendStringInfoChar(es->str, '\n');
 		}
 	}
 }
@@ -3182,12 +3200,16 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 		{
 			ExplainIndentText(es);
 			appendStringInfo(es->str,
-							 "Hits: " UINT64_FORMAT "  Misses: " UINT64_FORMAT "  Evictions: " UINT64_FORMAT "  Overflows: " UINT64_FORMAT "  Memory Usage: " INT64_FORMAT "kB\n",
+							 "Hits: " UINT64_FORMAT "  Misses: " UINT64_FORMAT "  Evictions: " UINT64_FORMAT "  Overflows: " UINT64_FORMAT,
 							 mstate->stats.cache_hits,
 							 mstate->stats.cache_misses,
 							 mstate->stats.cache_evictions,
-							 mstate->stats.cache_overflows,
+							 mstate->stats.cache_overflows);
+			if (es->machine)
+				appendStringInfo(es->str, "  Memory Usage: " INT64_FORMAT "kB",
 							 memPeakKb);
+
+			appendStringInfoChar(es->str, '\n');
 		}
 	}
 
@@ -3256,13 +3278,16 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
 	Agg		   *agg = (Agg *) aggstate->ss.ps.plan;
 	int64		memPeakKb = (aggstate->hash_mem_peak + 1023) / 1024;
 
+	/* XXX: there's nothing portable we can show here ? */
+	if (!es->machine)
+		return;
+
 	if (agg->aggstrategy != AGG_HASHED &&
 		agg->aggstrategy != AGG_MIXED)
 		return;
 
 	if (es->format != EXPLAIN_FORMAT_TEXT)
 	{
-
 		if (es->costs)
 			ExplainPropertyInteger("Planned Partitions", NULL,
 								   aggstate->hash_planned_partitions, es);
@@ -3375,6 +3400,10 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
 static void
 show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es)
 {
+	/* XXX: there's nothing portable we can show here ? */
+	if (!es->machine)
+		return;
+
 	if (es->format != EXPLAIN_FORMAT_TEXT)
 	{
 		ExplainPropertyInteger("Exact Heap Blocks", NULL,
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index ce8c458d731..931dad08cbc 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -46,6 +46,7 @@ typedef struct ExplainState
 	bool		timing;			/* print detailed node timing */
 	bool		summary;		/* print total planning and execution timing */
 	bool		settings;		/* print modified settings */
+	bool		machine;		/* print memory/disk and other machine-specific output */
 	ExplainFormat format;		/* output format */
 	/* state for output formatting --- not reset for each new plan tree */
 	int			indent;			/* current indentation level */
diff --git a/src/test/isolation/expected/horizons.out b/src/test/isolation/expected/horizons.out
index 4150b2dee64..ee3e495a646 100644
--- a/src/test/isolation/expected/horizons.out
+++ b/src/test/isolation/expected/horizons.out
@@ -24,7 +24,7 @@ Index Only Scan using horizons_tst_data_key on horizons_tst
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -34,7 +34,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -47,7 +47,7 @@ step pruner_delete:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -57,7 +57,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -94,7 +94,7 @@ Index Only Scan using horizons_tst_data_key on horizons_tst
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -104,7 +104,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -117,7 +117,7 @@ step pruner_delete:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -127,7 +127,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -156,7 +156,7 @@ step ll_start:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -166,7 +166,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -180,7 +180,7 @@ step pruner_delete:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -190,7 +190,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -220,7 +220,7 @@ step ll_start:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -230,7 +230,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -246,7 +246,7 @@ step pruner_vacuum:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -256,7 +256,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -285,7 +285,7 @@ step ll_start:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -295,7 +295,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -311,7 +311,7 @@ step pruner_vacuum:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
@@ -321,7 +321,7 @@ step pruner_query:
 
 step pruner_query: 
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 
 ?column?
diff --git a/src/test/isolation/specs/horizons.spec b/src/test/isolation/specs/horizons.spec
index d5239ff2287..082205d36ba 100644
--- a/src/test/isolation/specs/horizons.spec
+++ b/src/test/isolation/specs/horizons.spec
@@ -82,7 +82,7 @@ step pruner_vacuum
 step pruner_query
 {
     SELECT explain_json($$
-        EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+        EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
 	  SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
 }
 
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 188dc7ccec9..36c20e06809 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -276,6 +276,61 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8'
  ]
 (1 row)
 
+-- HashAgg
+begin;
+SET work_mem='64kB';
+select explain_filter('explain (analyze) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+                                                 explain_filter                                                 
+----------------------------------------------------------------------------------------------------------------
+ HashAggregate  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+   Group Key: a
+   Batches: N  Memory Usage: NkB  Disk Usage: NkB
+   ->  Function Scan on generate_series a  (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
+(6 rows)
+
+select explain_filter('explain (analyze, machine off) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+                                                 explain_filter                                                 
+----------------------------------------------------------------------------------------------------------------
+ HashAggregate  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+   Group Key: a
+   ->  Function Scan on generate_series a  (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
+(5 rows)
+
+rollback;
+-- Bitmap scan
+begin;
+SET enable_indexscan=no;
+CREATE TABLE explainbitmap AS SELECT i AS a FROM generate_series(1,999) AS i;
+ANALYZE explainbitmap;
+CREATE INDEX ON explainbitmap(a);
+select explain_filter('explain (analyze) SELECT * FROM explainbitmap WHERE a<9');
+                                                    explain_filter                                                    
+----------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on explainbitmap  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+   Recheck Cond: (a < N)
+   Heap Blocks: exact=N
+   ->  Bitmap Index Scan on explainbitmap_a_idx  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+         Index Cond: (a < N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(7 rows)
+
+select explain_filter('explain (analyze, machine off) SELECT * FROM explainbitmap WHERE a<9');
+                                                    explain_filter                                                    
+----------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on explainbitmap  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+   Recheck Cond: (a < N)
+   ->  Bitmap Index Scan on explainbitmap_a_idx  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+         Index Cond: (a < N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+rollback;
 -- SETTINGS option
 -- We have to ignore other settings that might be imposed by the environment,
 -- so printing the whole Settings field unfortunately won't do.
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 545e301e482..56a32df5369 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -542,7 +542,7 @@ select explain_analyze_without_memory('select * from (select * from t order by a
          Full-sort Groups: 2  Sort Methods: top-N heapsort, quicksort  Average Memory: NNkB  Peak Memory: NNkB
          ->  Sort (actual rows=101 loops=1)
                Sort Key: t.a
-               Sort Method: quicksort  Memory: NNkB
+               Sort Method: quicksort
                ->  Seq Scan on t (actual rows=1000 loops=1)
 (9 rows)
 
@@ -745,7 +745,7 @@ select explain_analyze_without_memory('select * from (select * from t order by a
          Pre-sorted Groups: 5  Sort Methods: top-N heapsort, quicksort  Average Memory: NNkB  Peak Memory: NNkB
          ->  Sort (actual rows=1000 loops=1)
                Sort Key: t.a
-               Sort Method: quicksort  Memory: NNkB
+               Sort Method: quicksort
                ->  Seq Scan on t (actual rows=1000 loops=1)
 (10 rows)
 
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 4ca0bd1f1e1..fbe6bed4330 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -21,9 +21,7 @@ begin
         end if;
         ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
         ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
-        ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
-	ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
-	ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
+        ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
         return next ln;
     end loop;
 end;
@@ -45,11 +43,10 @@ WHERE t2.unique1 < 1000;', false);
          ->  Memoize (actual rows=1 loops=N)
                Cache Key: t2.twenty
                Cache Mode: logical
-               Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+               Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
                      Index Cond: (unique1 = t2.twenty)
-                     Heap Fetches: N
-(12 rows)
+(11 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -75,11 +72,10 @@ WHERE t1.unique1 < 1000;', false);
          ->  Memoize (actual rows=1 loops=N)
                Cache Key: t1.twenty
                Cache Mode: logical
-               Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+               Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0
                ->  Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
                      Index Cond: (unique1 = t1.twenty)
-                     Heap Fetches: N
-(12 rows)
+(11 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -110,11 +106,10 @@ WHERE t2.unique1 < 1200;', true);
          ->  Memoize (actual rows=1 loops=N)
                Cache Key: t2.thousand
                Cache Mode: logical
-               Hits: N  Misses: N  Evictions: N  Overflows: 0  Memory Usage: NkB
+               Hits: N  Misses: N  Evictions: N  Overflows: 0
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
                      Index Cond: (unique1 = t2.thousand)
-                     Heap Fetches: N
-(12 rows)
+(11 rows)
 
 CREATE TABLE flt (f float);
 CREATE INDEX flt_f_idx ON flt (f);
@@ -128,15 +123,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
 -------------------------------------------------------------------------------
  Nested Loop (actual rows=4 loops=N)
    ->  Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
-         Heap Fetches: N
    ->  Memoize (actual rows=2 loops=N)
          Cache Key: f1.f
          Cache Mode: logical
-         Hits: 1  Misses: 1  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+         Hits: 1  Misses: 1  Evictions: Zero  Overflows: 0
          ->  Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
                Index Cond: (f = f1.f)
-               Heap Fetches: N
-(10 rows)
+(8 rows)
 
 -- Ensure memoize operates in binary mode
 SELECT explain_memoize('
@@ -145,15 +138,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
 -------------------------------------------------------------------------------
  Nested Loop (actual rows=4 loops=N)
    ->  Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
-         Heap Fetches: N
    ->  Memoize (actual rows=2 loops=N)
          Cache Key: f1.f
          Cache Mode: binary
-         Hits: 0  Misses: 2  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+         Hits: 0  Misses: 2  Evictions: Zero  Overflows: 0
          ->  Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
                Index Cond: (f <= f1.f)
-               Heap Fetches: N
-(10 rows)
+(8 rows)
 
 DROP TABLE flt;
 -- Exercise Memoize in binary mode with a large fixed width type and a
@@ -175,7 +166,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
    ->  Memoize (actual rows=4 loops=N)
          Cache Key: s1.n
          Cache Mode: binary
-         Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+         Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0
          ->  Index Scan using strtest_n_idx on strtest s2 (actual rows=4 loops=N)
                Index Cond: (n <= s1.n)
 (8 rows)
@@ -190,7 +181,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
    ->  Memoize (actual rows=4 loops=N)
          Cache Key: s1.t
          Cache Mode: binary
-         Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0  Memory Usage: NkB
+         Hits: 3  Misses: 3  Evictions: Zero  Overflows: 0
          ->  Index Scan using strtest_t_idx on strtest s2 (actual rows=4 loops=N)
                Index Cond: (t <= s1.t)
 (8 rows)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c779..cabadd48b81 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2479,7 +2479,6 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
                            Index Cond: (a = 1)
                ->  Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
                      Recheck Cond: (a = 1)
-                     Heap Blocks: exact=1
                      ->  Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
                            Index Cond: (a = 1)
                ->  Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
@@ -2494,14 +2493,13 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
                                  Index Cond: (a = 1)
                      ->  Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
                            Recheck Cond: (a = 1)
-                           Heap Blocks: exact=1
                            ->  Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
                                  Index Cond: (a = 1)
                      ->  Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
                            Recheck Cond: (a = 1)
                            ->  Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
                                  Index Cond: (a = 1)
-(34 rows)
+(32 rows)
 
 table ab;
  a | b 
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 4ea1aa7dfd4..9f36c627419 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -562,24 +562,11 @@ explain (analyze, timing off, summary off, costs off)
 
 alter table tenk2 reset (parallel_workers);
 reset work_mem;
-create function explain_parallel_sort_stats() returns setof text
-language plpgsql as
-$$
-declare ln text;
-begin
-    for ln in
-        explain (analyze, timing off, summary off, costs off)
-          select * from
+explain (analyze)
+select * from
           (select ten from tenk1 where ten < 100 order by ten) ss
-          right join (values (1),(2),(3)) v(x) on true
-    loop
-        ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
-        return next ln;
-    end loop;
-end;
-$$;
-select * from explain_parallel_sort_stats();
-                       explain_parallel_sort_stats                        
+          right join (values (1),(2),(3)) v(x) on true;
+                                QUERY PLAN                                
 --------------------------------------------------------------------------
  Nested Loop Left Join (actual rows=30000 loops=1)
    ->  Values Scan on "*VALUES*" (actual rows=3 loops=1)
@@ -588,11 +575,11 @@ select * from explain_parallel_sort_stats();
          Workers Launched: 4
          ->  Sort (actual rows=2000 loops=15)
                Sort Key: tenk1.ten
-               Sort Method: quicksort  Memory: xxx
-               Worker 0:  Sort Method: quicksort  Memory: xxx
-               Worker 1:  Sort Method: quicksort  Memory: xxx
-               Worker 2:  Sort Method: quicksort  Memory: xxx
-               Worker 3:  Sort Method: quicksort  Memory: xxx
+               Sort Method: quicksort
+               Worker 0:  Sort Method: quicksort
+               Worker 1:  Sort Method: quicksort
+               Worker 2:  Sort Method: quicksort
+               Worker 3:  Sort Method: quicksort
                ->  Parallel Seq Scan on tenk1 (actual rows=2000 loops=15)
                      Filter: (ten < 100)
 (14 rows)
@@ -603,7 +590,6 @@ reset enable_mergejoin;
 reset enable_material;
 reset effective_io_concurrency;
 drop table bmscantest;
-drop function explain_parallel_sort_stats();
 -- test parallel merge join path.
 set enable_hashjoin to off;
 set enable_nestloop to off;
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 45c75eecc5f..527f04e122b 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1550,27 +1550,15 @@ insert into sq_limit values
     (6, 2, 2),
     (7, 3, 3),
     (8, 4, 4);
-create function explain_sq_limit() returns setof text language plpgsql as
-$$
-declare ln text;
-begin
-    for ln in
-        explain (analyze, summary off, timing off, costs 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');
-        return next ln;
-    end loop;
-end;
-$$;
-select * from explain_sq_limit();
-                        explain_sq_limit                        
+explain (analyze)
+        select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
+                           QUERY PLAN                           
 ----------------------------------------------------------------
  Limit (actual rows=3 loops=1)
    ->  Subquery Scan on x (actual rows=3 loops=1)
          ->  Sort (actual rows=3 loops=1)
                Sort Key: sq_limit.c1, sq_limit.pk
-               Sort Method: top-N heapsort  Memory: xxx
+               Sort Method: top-N heapsort
                ->  Seq Scan on sq_limit (actual rows=8 loops=1)
 (6 rows)
 
@@ -1582,7 +1570,6 @@ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
   2 |  2
 (3 rows)
 
-drop function explain_sq_limit();
 drop table sq_limit;
 --
 -- Ensure that backward scan direction isn't propagated into
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index f91d1004d5e..c38857d3461 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -72,6 +72,23 @@ select explain_filter('explain (analyze, buffers, format yaml) select * from int
 select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
 select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
 
+-- HashAgg
+begin;
+SET work_mem='64kB';
+select explain_filter('explain (analyze) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+select explain_filter('explain (analyze, machine off) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+rollback;
+
+-- Bitmap scan
+begin;
+SET enable_indexscan=no;
+CREATE TABLE explainbitmap AS SELECT i AS a FROM generate_series(1,999) AS i;
+ANALYZE explainbitmap;
+CREATE INDEX ON explainbitmap(a);
+select explain_filter('explain (analyze) SELECT * FROM explainbitmap WHERE a<9');
+select explain_filter('explain (analyze, machine off) SELECT * FROM explainbitmap WHERE a<9');
+rollback;
+
 -- SETTINGS option
 -- We have to ignore other settings that might be imposed by the environment,
 -- so printing the whole Settings field unfortunately won't do.
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index c6ed5a2aa66..1368f8dd185 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -22,9 +22,7 @@ begin
         end if;
         ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
         ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
-        ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
-	ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
-	ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
+        ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
         return next ln;
     end loop;
 end;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index f9247312484..fc586161235 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -221,23 +221,10 @@ explain (analyze, timing off, summary off, costs off)
 alter table tenk2 reset (parallel_workers);
 
 reset work_mem;
-create function explain_parallel_sort_stats() returns setof text
-language plpgsql as
-$$
-declare ln text;
-begin
-    for ln in
-        explain (analyze, timing off, summary off, costs off)
-          select * from
+explain (analyze)
+select * from
           (select ten from tenk1 where ten < 100 order by ten) ss
-          right join (values (1),(2),(3)) v(x) on true
-    loop
-        ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
-        return next ln;
-    end loop;
-end;
-$$;
-select * from explain_parallel_sort_stats();
+          right join (values (1),(2),(3)) v(x) on true;
 
 reset enable_indexscan;
 reset enable_hashjoin;
@@ -245,7 +232,6 @@ reset enable_mergejoin;
 reset enable_material;
 reset effective_io_concurrency;
 drop table bmscantest;
-drop function explain_parallel_sort_stats();
 
 -- test parallel merge join path.
 set enable_hashjoin to off;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 94ba91f5bb3..2311c9c0ed8 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -807,26 +807,11 @@ insert into sq_limit values
     (7, 3, 3),
     (8, 4, 4);
 
-create function explain_sq_limit() returns setof text language plpgsql as
-$$
-declare ln text;
-begin
-    for ln in
-        explain (analyze, summary off, timing off, costs 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');
-        return next ln;
-    end loop;
-end;
-$$;
-
-select * from explain_sq_limit();
+explain (analyze)
+        select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
 
 select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
 
-drop function explain_sq_limit();
-
 drop table sq_limit;
 
 --
-- 
2.17.1

>From 6fdb83797005375b4bb7851b9f7397907c373635 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Tue, 16 Nov 2021 11:22:40 -0600
Subject: [PATCH 5/7] f!Rows removed by filter

This cleans one more kludge in partition_prune, but drags in 2 more files...
---
 .../postgres_fdw/expected/postgres_fdw.out    |  6 ++--
 src/backend/commands/explain.c                | 36 +++++++++----------
 src/test/regress/expected/memoize.out         |  9 ++---
 src/test/regress/expected/partition_prune.out | 29 +++++----------
 src/test/regress/expected/select_parallel.out |  4 +--
 src/test/regress/sql/partition_prune.sql      |  1 -
 6 files changed, 32 insertions(+), 53 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7d6f7d9e3df..e8e83ca5ad5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10496,13 +10496,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c
  Nested Loop (actual rows=1 loops=1)
    ->  Seq Scan on local_tbl (actual rows=1 loops=1)
          Filter: (c = 'bar'::text)
-         Rows Removed by Filter: 1
    ->  Append (actual rows=1 loops=1)
          ->  Async Foreign Scan on async_p1 async_pt_1 (never executed)
          ->  Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1)
          ->  Seq Scan on async_p3 async_pt_3 (never executed)
                Filter: (local_tbl.a = a)
-(9 rows)
+(8 rows)
 
 SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar';
   a   |  b  |  c  |  a   |  b  |  c   
@@ -10636,8 +10635,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
                Filter: (b === 505)
          ->  Seq Scan on async_p3 t1_3 (actual rows=1 loops=1)
                Filter: (b === 505)
-               Rows Removed by Filter: 101
-(9 rows)
+(8 rows)
 
 SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
   a   |  b  |  c   
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8b4ff9624d4..a0212e7d241 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1765,7 +1765,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_scan_qual(((IndexScan *) plan)->indexorderbyorig,
 						   "Order By", planstate, ancestors, es);
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1778,7 +1778,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_scan_qual(((IndexOnlyScan *) plan)->indexorderby,
 						   "Order By", planstate, ancestors, es);
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			if (es->analyze && es->machine)
@@ -1796,7 +1796,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Index Recheck", 2,
 										   planstate, es);
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			if (es->analyze)
@@ -1814,7 +1814,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 		case T_WorkTableScan:
 		case T_SubqueryScan:
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1823,7 +1823,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				Gather	   *gather = (Gather *) plan;
 
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 				ExplainPropertyInteger("Workers Planned", NULL,
@@ -1851,7 +1851,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				GatherMerge *gm = (GatherMerge *) plan;
 
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 				ExplainPropertyInteger("Workers Planned", NULL,
@@ -1889,7 +1889,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 								es->verbose, es);
 			}
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1903,7 +1903,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 								es->verbose, es);
 			}
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1919,7 +1919,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					tidquals = list_make1(make_orclause(tidquals));
 				show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es);
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 			}
@@ -1936,14 +1936,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					tidquals = list_make1(make_andclause(tidquals));
 				show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es);
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 			}
 			break;
 		case T_ForeignScan:
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			show_foreignscan_info((ForeignScanState *) planstate, es);
@@ -1953,7 +1953,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				CustomScanState *css = (CustomScanState *) planstate;
 
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 				if (css->methods->ExplainCustomScan)
@@ -1967,7 +1967,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Join Filter", 1,
 										   planstate, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 2,
 										   planstate, es);
 			break;
@@ -1980,7 +1980,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Join Filter", 1,
 										   planstate, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 2,
 										   planstate, es);
 			break;
@@ -1993,7 +1993,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Join Filter", 1,
 										   planstate, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 2,
 										   planstate, es);
 			break;
@@ -2001,14 +2001,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_agg_keys(castNode(AggState, planstate), ancestors, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
 			show_hashagg_info((AggState *) planstate, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
 		case T_Group:
 			show_group_keys(castNode(GroupState, planstate), ancestors, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -2030,7 +2030,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_upper_qual((List *) ((Result *) plan)->resconstantqual,
 							"One-Time Filter", planstate, ancestors, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index fbe6bed4330..3e521f3487e 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false);
    ->  Nested Loop (actual rows=1000 loops=N)
          ->  Seq Scan on tenk1 t2 (actual rows=1000 loops=N)
                Filter: (unique1 < 1000)
-               Rows Removed by Filter: 9000
          ->  Memoize (actual rows=1 loops=N)
                Cache Key: t2.twenty
                Cache Mode: logical
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
                      Index Cond: (unique1 = t2.twenty)
-(11 rows)
+(10 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false);
    ->  Nested Loop (actual rows=1000 loops=N)
          ->  Seq Scan on tenk1 t1 (actual rows=1000 loops=N)
                Filter: (unique1 < 1000)
-               Rows Removed by Filter: 9000
          ->  Memoize (actual rows=1 loops=N)
                Cache Key: t1.twenty
                Cache Mode: logical
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0
                ->  Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
                      Index Cond: (unique1 = t1.twenty)
-(11 rows)
+(10 rows)
 
 -- And check we get the expected results.
 SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -102,14 +100,13 @@ WHERE t2.unique1 < 1200;', true);
    ->  Nested Loop (actual rows=1200 loops=N)
          ->  Seq Scan on tenk1 t2 (actual rows=1200 loops=N)
                Filter: (unique1 < 1200)
-               Rows Removed by Filter: 8800
          ->  Memoize (actual rows=1 loops=N)
                Cache Key: t2.thousand
                Cache Mode: logical
                Hits: N  Misses: N  Evictions: N  Overflows: 0
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
                      Index Cond: (unique1 = t2.thousand)
-(11 rows)
+(10 rows)
 
 CREATE TABLE flt (f float);
 CREATE INDEX flt_f_idx ON flt (f);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index cabadd48b81..3576e65bc29 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
  Append (actual rows=0 loops=1)
    ->  Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1)
          Filter: (a = (list_part_fn(1) + a))
-         Rows Removed by Filter: 1
    ->  Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1)
          Filter: (a = (list_part_fn(1) + a))
-         Rows Removed by Filter: 1
    ->  Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1)
          Filter: (a = (list_part_fn(1) + a))
-         Rows Removed by Filter: 1
    ->  Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1)
          Filter: (a = (list_part_fn(1) + a))
-         Rows Removed by Filter: 1
-(13 rows)
+(9 rows)
 
 rollback;
 drop table list_part;
@@ -1957,7 +1953,6 @@ begin
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
         ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
-        ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
         return next ln;
     end loop;
 end;
@@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                ->  Nested Loop (actual rows=N loops=N)
                      ->  Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
                            Filter: (a = ANY ('{1,0,0}'::integer[]))
-                           Rows Removed by Filter: N
                      ->  Append (actual rows=N loops=N)
                            ->  Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
@@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
                                  Index Cond: (a = a.a)
-(28 rows)
+(27 rows)
 
 delete from lprt_a where a = 1;
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
@@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                ->  Nested Loop (actual rows=N loops=N)
                      ->  Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
                            Filter: (a = ANY ('{1,0,0}'::integer[]))
-                           Rows Removed by Filter: N
                      ->  Append (actual rows=N loops=N)
                            ->  Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed)
                                  Index Cond: (a = a.a)
@@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
                                  Index Cond: (a = a.a)
-(28 rows)
+(27 rows)
 
 reset enable_hashjoin;
 reset enable_mergejoin;
@@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
          Filter: ((a = $1) AND (b = $0))
    ->  Seq Scan on xy_1 (actual rows=0 loops=1)
          Filter: ((x = $1) AND (y = $0))
-         Rows Removed by Filter: 1
    ->  Seq Scan on ab_a1_b1 ab_4 (never executed)
          Filter: ((a = $1) AND (b = $0))
    ->  Seq Scan on ab_a1_b2 ab_5 (never executed)
          Filter: ((a = $1) AND (b = $0))
    ->  Seq Scan on ab_a1_b3 ab_6 (never executed)
          Filter: ((a = $1) AND (b = $0))
-(19 rows)
+(18 rows)
 
 -- Ensure we see just the xy_1 row.
 execute ab_q6(100);
@@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value);
    InitPlan 1 (returns $0)
      ->  Seq Scan on boolvalues (actual rows=1 loops=1)
            Filter: value
-           Rows Removed by Filter: 1
    ->  Seq Scan on boolp_f boolp_1 (never executed)
          Filter: (a = $0)
    ->  Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1)
          Filter: (a = $0)
-(9 rows)
+(8 rows)
 
 explain (analyze, costs off, summary off, timing off)
 select * from boolp where a = (select value from boolvalues where not value);
@@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value);
    InitPlan 1 (returns $0)
      ->  Seq Scan on boolvalues (actual rows=1 loops=1)
            Filter: (NOT value)
-           Rows Removed by Filter: 1
    ->  Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1)
          Filter: (a = $0)
    ->  Seq Scan on boolp_t boolp_2 (never executed)
          Filter: (a = $0)
-(9 rows)
+(8 rows)
 
 drop table boolp;
 --
@@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
    Subplans Removed: 1
    ->  Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1)
          Filter: ((a >= $1) AND ((a % 10) = 5))
-         Rows Removed by Filter: 9
    ->  Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1)
          Filter: ((a >= $1) AND ((a % 10) = 5))
-         Rows Removed by Filter: 9
-(9 rows)
+(7 rows)
 
 execute mt_q1(15);
  a  
@@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
    Subplans Removed: 2
    ->  Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1)
          Filter: ((a >= $1) AND ((a % 10) = 5))
-         Rows Removed by Filter: 9
-(6 rows)
+(5 rows)
 
 execute mt_q1(25);
  a  
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 9f36c627419..02176d7e2f6 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off)
    ->  Nested Loop (actual rows=98000 loops=1)
          ->  Seq Scan on tenk2 (actual rows=10 loops=1)
                Filter: (thousand = 0)
-               Rows Removed by Filter: 9990
          ->  Gather (actual rows=9800 loops=10)
                Workers Planned: 4
                Workers Launched: 4
                ->  Parallel Seq Scan on tenk1 (actual rows=1960 loops=50)
                      Filter: (hundred > 1)
-                     Rows Removed by Filter: 40
-(11 rows)
+(9 rows)
 
 alter table tenk2 reset (parallel_workers);
 reset work_mem;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d70bd8610cb..e3938bea9c0 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -463,7 +463,6 @@ begin
     loop
         ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
         ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
-        ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
         return next ln;
     end loop;
 end;
-- 
2.17.1

>From b1d45202a0db2e703948c3c63c6001a779c4b432 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Tue, 16 Nov 2021 10:37:45 -0600
Subject: [PATCH 6/7] f!Workers Launched: ...

---
 src/backend/commands/explain.c                |  4 +-
 src/test/regress/expected/partition_prune.out | 38 ++++++-------------
 src/test/regress/expected/select_parallel.out |  9 ++---
 src/test/regress/sql/partition_prune.sql      |  1 -
 4 files changed, 17 insertions(+), 35 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a0212e7d241..bd846170daf 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1833,7 +1833,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				if (gather->initParam)
 					show_eval_params(gather->initParam, es);
 
-				if (es->analyze)
+				if (es->analyze && es->machine)
 				{
 					int			nworkers;
 
@@ -1861,7 +1861,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				if (gm->initParam)
 					show_eval_params(gm->initParam, es);
 
-				if (es->analyze)
+				if (es->analyze && es->machine)
 				{
 					int			nworkers;
 
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 3576e65bc29..97c576decd7 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1951,7 +1951,6 @@ begin
         execute format('explain (analyze, costs off, summary off, timing off) %s',
             $1)
     loop
-        ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
         ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
         return next ln;
     end loop;
@@ -1970,7 +1969,6 @@ select explain_parallel_append('execute ab_q4 (2, 2)');
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Parallel Append (actual rows=N loops=N)
                      Subplans Removed: 6
@@ -1980,7 +1978,7 @@ select explain_parallel_append('execute ab_q4 (2, 2)');
                            Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
                      ->  Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N)
                            Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
-(13 rows)
+(12 rows)
 
 -- Test run-time pruning with IN lists.
 prepare ab_q5 (int, int, int) as
@@ -1991,7 +1989,6 @@ select explain_parallel_append('execute ab_q5 (1, 1, 1)');
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Parallel Append (actual rows=N loops=N)
                      Subplans Removed: 6
@@ -2001,7 +1998,7 @@ select explain_parallel_append('execute ab_q5 (1, 1, 1)');
                            Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                      ->  Parallel Seq Scan on ab_a1_b3 ab_3 (actual rows=N loops=N)
                            Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-(13 rows)
+(12 rows)
 
 select explain_parallel_append('execute ab_q5 (2, 3, 3)');
                               explain_parallel_append                               
@@ -2009,7 +2006,6 @@ select explain_parallel_append('execute ab_q5 (2, 3, 3)');
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Parallel Append (actual rows=N loops=N)
                      Subplans Removed: 3
@@ -2025,7 +2021,7 @@ select explain_parallel_append('execute ab_q5 (2, 3, 3)');
                            Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
                      ->  Parallel Seq Scan on ab_a3_b3 ab_6 (actual rows=N loops=N)
                            Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-(19 rows)
+(18 rows)
 
 -- Try some params whose values do not belong to any partition.
 select explain_parallel_append('execute ab_q5 (33, 44, 55)');
@@ -2034,11 +2030,10 @@ select explain_parallel_append('execute ab_q5 (33, 44, 55)');
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Parallel Append (actual rows=N loops=N)
                      Subplans Removed: 9
-(7 rows)
+(6 rows)
 
 -- Test Parallel Append with PARAM_EXEC Params
 select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
@@ -2052,7 +2047,6 @@ select explain_parallel_append('select count(*) from ab where (a = (select 1) or
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
          Params Evaluated: $0, $1
-         Workers Launched: N
          ->  Parallel Append (actual rows=N loops=N)
                ->  Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
                      Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
@@ -2060,7 +2054,7 @@ select explain_parallel_append('select count(*) from ab where (a = (select 1) or
                      Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
                ->  Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
                      Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
-(16 rows)
+(15 rows)
 
 -- Test pruning during parallel nested loop query
 create table lprt_a (a int not null);
@@ -2087,7 +2081,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 1
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Nested Loop (actual rows=N loops=N)
                      ->  Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
@@ -2111,7 +2104,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
                                  Index Cond: (a = a.a)
-(27 rows)
+(26 rows)
 
 -- Ensure the same partitions are pruned when we make the nested loop
 -- parameter an Expr rather than a plain Param.
@@ -2121,7 +2114,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 1
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Nested Loop (actual rows=N loops=N)
                      ->  Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
@@ -2145,7 +2137,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = (a.a + 0))
                            ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
                                  Index Cond: (a = (a.a + 0))
-(27 rows)
+(26 rows)
 
 insert into lprt_a values(3),(3);
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)');
@@ -2154,7 +2146,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 1
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Nested Loop (actual rows=N loops=N)
                      ->  Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
@@ -2178,7 +2169,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-(27 rows)
+(26 rows)
 
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
                                         explain_parallel_append                                         
@@ -2186,7 +2177,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 1
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Nested Loop (actual rows=N loops=N)
                      ->  Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
@@ -2210,7 +2200,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
                                  Index Cond: (a = a.a)
-(27 rows)
+(26 rows)
 
 delete from lprt_a where a = 1;
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
@@ -2219,7 +2209,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 1
-         Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Nested Loop (actual rows=N loops=N)
                      ->  Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
@@ -2243,7 +2232,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
                                  Index Cond: (a = a.a)
-(27 rows)
+(26 rows)
 
 reset enable_hashjoin;
 reset enable_mergejoin;
@@ -3664,7 +3653,6 @@ select explain_parallel_append('select * from listp where a = (select 1);');
  Gather (actual rows=N loops=N)
    Workers Planned: 2
    Params Evaluated: $0
-   Workers Launched: N
    InitPlan 1 (returns $0)
      ->  Result (actual rows=N loops=N)
    ->  Parallel Append (actual rows=N loops=N)
@@ -3672,7 +3660,7 @@ select explain_parallel_append('select * from listp where a = (select 1);');
                Filter: (a = $0)
          ->  Parallel Seq Scan on listp_12_2 listp_2 (never executed)
                Filter: (a = $0)
-(11 rows)
+(10 rows)
 
 -- Like the above but throw some more complexity at the planner by adding
 -- a UNION ALL.  We expect both sides of the union not to scan the
@@ -3687,7 +3675,6 @@ select * from listp where a = (select 2);');
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
          Params Evaluated: $0
-         Workers Launched: N
          InitPlan 1 (returns $0)
            ->  Result (actual rows=N loops=N)
          ->  Parallel Append (actual rows=N loops=N)
@@ -3698,7 +3685,6 @@ select * from listp where a = (select 2);');
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
          Params Evaluated: $1
-         Workers Launched: N
          InitPlan 2 (returns $1)
            ->  Result (actual rows=N loops=N)
          ->  Parallel Append (actual rows=N loops=N)
@@ -3706,7 +3692,7 @@ select * from listp where a = (select 2);');
                      Filter: (a = $1)
                ->  Parallel Seq Scan on listp_12_2 listp_5 (actual rows=N loops=N)
                      Filter: (a = $1)
-(23 rows)
+(21 rows)
 
 drop table listp;
 reset parallel_tuple_cost;
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 02176d7e2f6..4c2e83c763e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -553,10 +553,9 @@ explain (analyze, timing off, summary off, costs off)
                Filter: (thousand = 0)
          ->  Gather (actual rows=9800 loops=10)
                Workers Planned: 4
-               Workers Launched: 4
                ->  Parallel Seq Scan on tenk1 (actual rows=1960 loops=50)
                      Filter: (hundred > 1)
-(9 rows)
+(8 rows)
 
 alter table tenk2 reset (parallel_workers);
 reset work_mem;
@@ -570,7 +569,6 @@ select * from
    ->  Values Scan on "*VALUES*" (actual rows=3 loops=1)
    ->  Gather Merge (actual rows=10000 loops=3)
          Workers Planned: 4
-         Workers Launched: 4
          ->  Sort (actual rows=2000 loops=15)
                Sort Key: tenk1.ten
                Sort Method: quicksort
@@ -580,7 +578,7 @@ select * from
                Worker 3:  Sort Method: quicksort
                ->  Parallel Seq Scan on tenk1 (actual rows=2000 loops=15)
                      Filter: (ten < 100)
-(14 rows)
+(13 rows)
 
 reset enable_indexscan;
 reset enable_hashjoin;
@@ -1032,9 +1030,8 @@ EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
 -------------------------------------------------------------
  Gather (actual rows=10000 loops=1)
    Workers Planned: 4
-   Workers Launched: 4
    ->  Parallel Seq Scan on tenk1 (actual rows=2000 loops=5)
-(4 rows)
+(3 rows)
 
 ROLLBACK TO SAVEPOINT settings;
 -- provoke error in worker
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index e3938bea9c0..24908a91f6c 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -461,7 +461,6 @@ begin
         execute format('explain (analyze, costs off, summary off, timing off) %s',
             $1)
     loop
-        ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
         ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
         return next ln;
     end loop;
-- 
2.17.1

>From da5503935475f80e1ce6498fb3fc283e341f67d5 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Tue, 16 Nov 2021 10:51:39 -0600
Subject: [PATCH 7/7] f!parallel rows

---
 src/backend/commands/explain.c | 13 +++++++++----
 1 file changed, 9 insertions(+), 4 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index bd846170daf..b824fadddcd 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1645,6 +1645,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 								 " (actual time=%.3f..%.3f rows=%.0f loops=%.0f)",
 								 startup_ms, total_ms, rows, nloops);
 			else
+				/* This is always shown for nonparallel output */
 				appendStringInfo(es->str,
 								 " (actual rows=%.0f loops=%.0f)",
 								 rows, nloops);
@@ -1673,8 +1674,12 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				ExplainPropertyFloat("Actual Startup Time", "ms", 0.0, 3, es);
 				ExplainPropertyFloat("Actual Total Time", "ms", 0.0, 3, es);
 			}
-			ExplainPropertyFloat("Actual Rows", NULL, 0.0, 0, es);
-			ExplainPropertyFloat("Actual Loops", NULL, 0.0, 0, es);
+
+			if (es->machine)
+			{
+				ExplainPropertyFloat("Actual Rows", NULL, 0.0, 0, es);
+				ExplainPropertyFloat("Actual Loops", NULL, 0.0, 0, es);
+			}
 		}
 	}
 
@@ -1710,7 +1715,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					appendStringInfo(es->str,
 									 "actual time=%.3f..%.3f rows=%.0f loops=%.0f\n",
 									 startup_ms, total_ms, rows, nloops);
-				else
+				else if (es->machine)
 					appendStringInfo(es->str,
 									 "actual rows=%.0f loops=%.0f\n",
 									 rows, nloops);
@@ -1724,7 +1729,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					ExplainPropertyFloat("Actual Total Time", "ms",
 										 total_ms, 3, es);
 				}
-				ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es);
+				ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); //
 				ExplainPropertyFloat("Actual Loops", NULL, nloops, 0, es);
 			}
 
-- 
2.17.1

---

From: Justin Pryzby <[email-masked]>
Date: Sat, 26 Feb 2022 15:07:20 -0600

Rebased over ebf6c5249b7db525e59563fb149642665c88f747.
It looks like that patch handles only query_id, and this patch also tries to
handle a bunch of other stuff.

If it's helpful, feel free to kick this patch to a future CF.
>From e58fffedc6f1cf471228fb3234faba35898678c3 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Sat, 22 Feb 2020 21:17:10 -0600
Subject: [PATCH 1/6] Add GUC: explain_regress

 src/bin/psql/describe.c                 |  2 +-
 12 files changed, 41 insertions(+), 9 deletions(-)

index 56654844e8..54da40d662 100644
@@ -3124,7 +3124,7 @@ estimate_path_cost_size(PlannerInfo *root,
index de81379da3..22a3cf6349 100644

 /*

 

 
 	query = castNode(Query, stmt->query);
 	if (IsQueryIdEnabled())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 1e3650184b..87266cf7bd 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -46,6 +46,7 @@
 #include "catalog/pg_authid.h"
 #include "catalog/storage.h"
 #include "commands/async.h"
+#include "commands/explain.h"
 #include "commands/prepare.h"
 #include "commands/tablespace.h"
 #include "commands/trigger.h"
@@ -1474,6 +1475,18 @@ static struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+
+	{
+		{"explain_regress", PGC_USERSET, DEVELOPER_OPTIONS,
+			gettext_noop("Change defaults of EXPLAIN to avoid unstable output."),
+			NULL,
+			GUC_NOT_IN_SAMPLE | GUC_EXPLAIN
+		},
+		&explain_regress,
+		false,
+		NULL, NULL, NULL
+	},
+
 	{
 		{"log_parser_stats", PGC_SUSET, STATS_MONITORING,
 			gettext_noop("Writes parser performance statistics to the server log."),
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e3382933d9..8f86fbbb00 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2628,7 +2628,7 @@ describeOneTableDetails(const char *schemaname,
 							  "stxstattarget\n"
 							  "FROM pg_catalog.pg_statistic_ext\n"
 							  "WHERE stxrelid = '%s'\n"
-							  "ORDER BY nsp, stxname;",
+							  "ORDER BY nsp, stxname",
 							  oid);
 
 			result = PSQLexec(buf.data);
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 666977fb1f..ce8c458d73 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -61,6 +61,8 @@ typedef struct ExplainState
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
 } ExplainState;
 
index 1734dfee8c..188dc7ccec 100644
index 2d49e765de..38fb5f94c6 100644
 
index 042316aeed..638abaaf80 100644
index db8427dd9b..01f820752d 100644

index 84549c78fa..f91d1004d5 100644
 
index 195aedb5ff..868ee58b80 100644

 drop table parted_tab;
 
index 6b954c9e50..4c91f60863 100644

>From f75e72b2489a0c7ce967972c0c181f8b8b5a61d3 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Mon, 15 Nov 2021 21:54:12 -0600
Subject: [PATCH 2/6] exercise explain_regress

index 313c72a268..e7ce5bb188 100644
 
index b79fe9a1c0..03f2e9e158 100644

index 13c3c360c2..de93145bf0 100644
 
 -- perform update
 
 -- perform update
 
 -- should error out
index 68b9ccfd45..e0b562933d 100644
index 689c448cc2..85bfb2bf16 100644
index 313e0fb9b6..3d1f447088 100644
 
-- 
2.17.1

>From aeddb8da4957d1d9469d46dd3372a699ef1e5350 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Wed, 22 Jul 2020 19:20:40 -0500
Subject: [PATCH 3/6] Make explain default to BUFFERS TRUE

---

index d3029f85ef..236e12596c 100644
index 7ed8c82a9d..b3a6f55ba4 100644
@@ -7833,7 +7833,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
index 9cf8ebea80..6e815e3a9a 100644
 
    <para>
 
 <screen>
index 4d758fb237..ceb0f4c83a 100644
index 22a3cf6349..d953ddf5be 100644

 
 
-- 
2.17.1

>From 3577649a7b3458bb7dd790916966f186134bf29b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Sat, 22 Feb 2020 18:45:22 -0600
Subject: [PATCH 4/6] Add explain(MACHINE) to hide machine-dependent output..

index d953ddf5be..71d6921a04 100644

+	/* if the machine option was not set explicitly, set default value */
+	es->machine = (machine_set) ? es->machine : !explain_regress;
+
 	query = castNode(Query, stmt->query);
 	if (IsQueryIdEnabled())
 		jstate = JumbleQuery(query, pstate->p_sourcetext);
@@ -627,7 +636,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 	 * generated by regression test suites.
 	 */
 	if (es->verbose && plannedstmt->queryId != UINT64CONST(0) &&
-		compute_query_id != COMPUTE_QUERY_ID_REGRESS)
+		compute_query_id != COMPUTE_QUERY_ID_REGRESS && es->machine)
 	{
 		/*
 		 * Output the queryid as an int64 rather than a uint64 so we match
@@ -638,7 +647,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 	}
 
@@ -1778,7 +1787,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
@@ -2752,8 +2761,12 @@ show_sort_info(SortState *sortstate, ExplainState *es)
@@ -2797,8 +2810,12 @@ show_sort_info(SortState *sortstate, ExplainState *es)
@@ -3086,25 +3103,26 @@ show_hash_info(HashState *hashstate, ExplainState *es)
@@ -3188,12 +3206,16 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
 
@@ -3262,13 +3284,16 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
 	Agg		   *agg = (Agg *) aggstate->ss.ps.plan;
 	int64		memPeakKb = (aggstate->hash_mem_peak + 1023) / 1024;

@@ -3381,6 +3406,10 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
index ce8c458d73..931dad08cb 100644
index 4150b2dee6..ee3e495a64 100644
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
 
 step pruner_query: 
     SELECT explain_json($$
 
 ?column?
index d5239ff228..082205d36b 100644
 
index 188dc7ccec..36c20e0680 100644
 
+-- HashAgg
+begin;
+SET work_mem='64kB';
index 545e301e48..56a32df536 100644

index 00438eb1ea..1b1557ce9f 100644

@@ -111,11 +107,10 @@ WHERE t2.unique1 < 1200;', true);
 
@@ -129,15 +124,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
 
@@ -146,15 +139,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
 
 DROP TABLE flt;
@@ -176,7 +167,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
@@ -191,7 +182,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
index 7555764c77..cabadd48b8 100644
 
 table ab;
  a | b 
index 4ea1aa7dfd..9f36c62741 100644
 
index 45c75eecc5..527f04e122 100644

index f91d1004d5..c38857d346 100644
 
+-- HashAgg
+begin;
+SET work_mem='64kB';
index 0979bcdf76..5d3e37f92d 100644
index f924731248..fc58616123 100644
 
 reset work_mem;
 
 reset enable_indexscan;
 reset enable_hashjoin;
 
index 94ba91f5bb..2311c9c0ed 100644

 
 
 --
-- 
2.17.1

>From a33df91a219c74318bb355369bfa7da5a25d3924 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Tue, 16 Nov 2021 11:22:40 -0600
Subject: [PATCH 5/6] f!Rows removed by filter

index f210f91188..1d03796c54 100644

 SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
   a   |  b  |  c   
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 71d6921a04..1b9976255c 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1771,7 +1771,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_scan_qual(((IndexScan *) plan)->indexorderbyorig,
 						   "Order By", planstate, ancestors, es);
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1784,7 +1784,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_scan_qual(((IndexOnlyScan *) plan)->indexorderby,
 						   "Order By", planstate, ancestors, es);
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			if (es->analyze && es->machine)
@@ -1802,7 +1802,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Index Recheck", 2,
 										   planstate, es);
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			if (es->analyze)
@@ -1820,7 +1820,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 		case T_WorkTableScan:
 		case T_SubqueryScan:
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1829,7 +1829,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				Gather	   *gather = (Gather *) plan;
 
@@ -1857,7 +1857,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 				ExplainPropertyInteger("Workers Planned", NULL,
@@ -1895,7 +1895,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 								es->verbose, es);
 			}
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1909,7 +1909,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 								es->verbose, es);
 			}
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -1925,7 +1925,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					tidquals = list_make1(make_orclause(tidquals));
 				show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es);
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 			}
@@ -1942,14 +1942,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
 					tidquals = list_make1(make_andclause(tidquals));
 				show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es);
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 			}
 			break;
 		case T_ForeignScan:
 			show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			show_foreignscan_info((ForeignScanState *) planstate, es);
@@ -1959,7 +1959,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				CustomScanState *css = (CustomScanState *) planstate;
 
 				show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
-				if (plan->qual)
+				if (plan->qual && es->machine)
 					show_instrumentation_count("Rows Removed by Filter", 1,
 											   planstate, es);
 				if (css->methods->ExplainCustomScan)
@@ -1973,7 +1973,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Join Filter", 1,
 										   planstate, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 2,
 										   planstate, es);
 			break;
@@ -1986,7 +1986,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Join Filter", 1,
 										   planstate, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 2,
 										   planstate, es);
 			break;
@@ -1999,7 +1999,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				show_instrumentation_count("Rows Removed by Join Filter", 1,
 										   planstate, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 2,
 										   planstate, es);
 			break;
@@ -2007,14 +2007,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_agg_keys(castNode(AggState, planstate), ancestors, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
 			show_hashagg_info((AggState *) planstate, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
 		case T_Group:
 			show_group_keys(castNode(GroupState, planstate), ancestors, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
@@ -2036,7 +2036,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 			show_upper_qual((List *) ((Result *) plan)->resconstantqual,
 							"One-Time Filter", planstate, ancestors, es);
 			show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
-			if (plan->qual)
+			if (plan->qual && es->machine)
 				show_instrumentation_count("Rows Removed by Filter", 1,
 										   planstate, es);
 			break;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 1b1557ce9f..7f4b73fd42 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false);
    ->  Nested Loop (actual rows=1000 loops=N)
          ->  Seq Scan on tenk1 t2 (actual rows=1000 loops=N)
                Filter: (unique1 < 1000)
-               Rows Removed by Filter: 9000
          ->  Memoize (actual rows=1 loops=N)
                Cache Key: t2.twenty
                Cache Mode: logical
                Hits: 980  Misses: 20  Evictions: Zero  Overflows: 0
                ->  Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
                      Index Cond: (unique1 = t2.twenty)
-(11 rows)
+(10 rows)

@@ -103,14 +101,13 @@ WHERE t2.unique1 < 1200;', true);
 
index cabadd48b8..3576e65bc2 100644
 
 rollback;
 drop table list_part;

 reset enable_hashjoin;
 reset enable_mergejoin;

 
 drop table boolp;
 
 execute mt_q1(15);
  a  
 
 execute mt_q1(25);
  a  
index 9f36c62741..02176d7e2f 100644
 
index d70bd8610c..e3938bea9c 100644

>From 9fceff8fc187ad057e319ad2af6b1a6179b67a79 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Tue, 16 Nov 2021 10:37:45 -0600
Subject: [PATCH 6/6] f!Workers Launched: ...

---

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 1b9976255c..5083a77cb1 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1839,7 +1839,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				if (gather->initParam)
 					show_eval_params(gather->initParam, es);
 
-				if (es->analyze)
 
@@ -1867,7 +1867,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
 				if (gm->initParam)
 					show_eval_params(gm->initParam, es);
 
-				if (es->analyze)
 
index 3576e65bc2..97c576decd 100644

 

 

 
 
 reset enable_hashjoin;
 reset enable_mergejoin;

 drop table listp;
index 02176d7e2f..4c2e83c763 100644

 reset enable_indexscan;
 reset enable_hashjoin;
 
index e3938bea9c..24908a91f6 100644

---

From: Justin Pryzby <[email-masked]>
Date: Wed, 30 Mar 2022 14:21:05 -0500

> If it's helpful, feel free to kick this patch to a future CF.

Rebased over MERGE.

---

From: Justin Pryzby <[email-masked]>
Date: Thu, 7 Jul 2022 01:49:04 -0500

@cfbot: rebased

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 26 Jul 2022 15:38:53 -0700

> >  2. enable explain(BUFFERS) by default (but disabled by explain_regress);

+1

> >     Maybe it should be called something else like "QUIET" or

INCIDENTALS ?

Aside from being 4 syllables and, for me at least, a pain to remember how
to spell, it seems to fit.

RUNTIME is probably easier, and we just need to point out the difficulty in
naming things since actual rows is still shown, and timings have their own
independent option.

> > be rephrased with (3) first.

I like the idea of encapsulating the logic about what to show into the
generation code instead of a post-processing routine.

I don't see any particular ordering of the three changes being most clear.

> > "COSTS ON" in postgres_fdw.c is considered to be a poor fix

Given that we have version tests scattered throughout the postgres_fdw.c
code I'd say protect it with version >= 9.0 and call it good.

But I'm assuming that we are somehow also sending over the GUC to the
remote server (which will be v16+) but I am unsure why that would be the
case.  I'm done code reading for now so I'm leaving this an open
request/question.

, then I suppose
> > ~100 regression tests which use EXPLAIN ANALYZE.

I'm not following the transition from the prior sentences about COSTS to
this one regarding BUFFERS.

  (3) still seems useful on its
> > own.

It is an orthogonal feature with a different section of our user base being
catered to, so I'd agree by default.

Patch Review Comments:

The following change in the machine commit seems out-of-place; are we
fixing a bug here?

explain.c:

Typo (trailing comment // without comment):

ExplainPropertyFloat("Actual Rows", NULL, rows, 0, es); //

I did a pretty thorough look-through of the locations of the various
changes and everything seems consistent with the established code in this
area (took me a bit to get to the level of comprehension though).  Whether
there may be something missing I'm less able to judge.

---

From: Justin Pryzby <[email-masked]>
Date: Mon, 5 Sep 2022 06:20:31 -0500

> this one regarding BUFFERS.

In this patch, SET explain_regress=on changes to defaults to
explain(COSTS OFF), so postgres_fdw now explicitly uses COSTS ON.  The
patch also changes default to (BUFFERS OFF), which is what allows
enabling buffers by default.  I think I was just saying that the
enabling buffers by default depends on somehow disabling it in
regression tests (preferably not by adding 100 instances of "BUFFERS
OFF").

> + if (bufusage && es->buffers)

I think that was an extraneous change, maybe from a conflict while
re-ordering the patches.  Removed it and rebased.  Thanks for looking.

-- 
Justin

---

From: Justin Pryzby <[email-masked]>
Date: Thu, 20 Oct 2022 21:09:34 -0500

Rebased.

BTW, I think it may be that the GUC should be marked PGDLLIMPORT ?

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 25 Oct 2022 15:49:14 +0200

> Rebased.

I had a look at the patch set.

It applies and builds cleanly and passes the regression tests.

0001: Add GUC: explain_regress

  I like the idea of the "explain_regress" GUC.  That should simplify
  the regression tests.

  @@ -625,7 +625,7 @@ initialize_environment(void)
 
  @@ -2288,6 +2288,7 @@ regression_main(int argc, char *argv[],

  This code comment is a leftover and should go.

0002: exercise explain_regress

  This is the promised simplification.  Looks good.

0003: Make explain default to BUFFERS TRUE

  Yes, please!
  This patch is independent from the previous patches.
  I'd like this to be applied, even if the GUC is rejected.

  (I understand that that would cause more changes in the regression
  test output if we changed that without introducing "explain_regress".)

  The patch changes the default value of "auto_explain.log_buffers"
  to "on", which makes sense.  However, the documentation in
  doc/src/sgml/auto-explain.sgml should be updated to reflect that.

      <para>
 
   <screen>

  This is not enough.  The patch would have to update all the examples that use EXPLAIN ANALYZE.
  I see two options:

  1. Change the output of all the examples and move this explanation to the first example
     with EXPLAIN ANALYZE:

       The numbers in the <literal>Buffers:</literal> line help to identify which parts
       of the query are the most I/O-intensive.

  2. Change all examples that currently do *not* use BUFFERS to EXPLAIN (BUFFERS OFF).
     Then you could change the example that shows BUFFERS to something like

       If you do not suppress the <literal>BUFFERS</literal> option that can be used with
       <command>EXPLAIN (ANALYZE)</command>, you get even more run time statistics:

0004, 0005, 0006, 0007: EXPLAIN (MACHINE)

  I think it is confusing that these are included in this patch set.
  EXPLAIN (MACHINE OFF) is similar to "explain_regress = on", only it goes even further:
  no query ID, no Heap Fetches, no Sort details, ...
  Why not add this functionality to the GUC?

  0005 suppresses "rows removed by filter", but how is that machine dependent?

> BTW, I think it may be that the GUC should be marked PGDLLIMPORT ?

I think it is project policy to apply this mark wherever it is needed.  Do you think
that third-party extensions might need to use this in C code?

Yours,
Laurenz Albe

---

From: Justin Pryzby <[email-masked]>
Date: Fri, 28 Oct 2022 17:59:04 -0500

> I had a look at the patch set.

Thanks for looking

>   This code comment is a leftover and should go.

No - I was wondering whether the GUC should be specified by the
environment or by the file; I think it's better by file.  Then it also
needs to be in Cluster.pm.

>   Why not add this functionality to the GUC?

Good question, and one I've asked myself.

explain_regress affects pre-existing uses of explain in the regression
tests, aiming to simplify current (or maybe only future) uses.  And
is obviously used to allow enabling BUFFERS by default.

explain(MACHINE off) is mostly about allowing "explain ANALYZE" to be
used in regression tests.  Which, right now, is rare.  Maybe I shouldn't
include those patches until the earlier patches progress (or, maybe we
should just defer their discussion).

>   0005 suppresses "rows removed by filter", but how is that machine dependent?

It can vary with the number of parallel workers (see 13e8b2ee8), which
may not be "machine dependent" but the point of that patch is to allow
predictable output of EXPLAIN ANALYZE.  Maybe it needs a better name (or
maybe it should be folded into the first patch).

I'm not actually sure if this patch should try to address parallel
workers at all, or (if so) if what it's doing now is adequate.

> that third-party extensions might need to use this in C code?

Since v15 (8ec569479), the policy is:
| On Windows, export all the server's global variables using PGDLLIMPORT markers (Robert Haas)

I'm convinced now that's what's intended here.

> that use EXPLAIN ANALYZE.

Fair enough.  I've left a comment to handle this later if the patch
gains any traction and 001 is progressed.

-- 
Justin

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 04 Nov 2022 11:46:03 +0100

Thanks for the updated patch set!

> should just defer their discussion).

Essentially, "explain_regress" is to simplify the current regression tests,
and MACHINE OFF is to simplify future regression tests.  That does not seem
like a meaningful distinction to me.  Both are only useful for the regression
tests, and I see no need for two different knobs.

My opinion is now like this:

+1 on enabling BUFFERS by default for EXPLAIN (ANALYZE)

+0.2 on "explain_regress"

-1 on EXPLAIN (MACHINE) in addition to "explain_regress"

-0.1 on adding the MACHINE OFF functionality to "explain_regress"

"explain_regress" reduces the EXPLAIN options you need for regression tests.
This is somewhat useful, but not a big win.  Also, it will make backpatching
regression tests slightly harder for the next 5 years.

Hence the +0.2 for "explain_regress".

For me, an important question is: do we really want more EXPLAIN (ANALYZE)
in the regression tests?  It will probably slow the tests down, and I wonder
if there is a lot of added value, if we have to remove all the machine-specific
output anyway.

Hence the -0.1.

> maybe it should be folded into the first patch).

Now it makes sense to me.  I just didn't think of that.

> > On Windows, export all the server's global variables using PGDLLIMPORT markers (Robert Haas)
> 

You convinced me too.

> gains any traction and 001 is progressed.

I agree with that.

I would really like to see 0003 go in, but it currently depends on 0001, which
I am not so sure about.
I understand that you did that so that "explain_regress" can turn off BUFFERS
and there is no extra churn in the regression tests.

Still, it would be a shame if resistance against "explain_regress" would
be a show-stopper for 0003.

If I could get my way, I'd want two separate patches: first, one to turn
BUFFERS on, and second one for "explain_regress" with its current functionality
on top of that.

Yours,
Laurenz Albe

---

From: Justin Pryzby <[email-masked]>
Date: Sat, 5 Nov 2022 08:44:25 -0500

> regression tests slightly harder for the next 5 years.

But it doesn't cause backpatching to be harder; if a patch is meant to
be backpatched, its author can still choose to write the old way, with
(COSTS OFF, ...)

> if there is a lot of added value, if we have to remove all the machine-specific

I don't intend to encourage putting lots of "explain analyze" in the
tests.  Rather, this makes that a reasonable possibility rather than
something that people are discouraged from doing by its difficulty.
Using "explain analyze" still needs to be evaluated (for speed and
otherwise), same as always.

In any case, I suggested to defer review of patches 004 and beyond,
which are optional, and it distracts from the essential discussion about
001.

> BUFFERS on, and second one for "explain_regress" with its current functionality

You set the patch to "waiting on author", which indicates that there's
no need for further input or review.  But, I think that's precisely
what's needed - without input from more people, what could I do to
progress the patch ?  I don't think it's reasonable to put 001 first and
change thousands (actually, 1338) of regression results.  If nobody
wants to discuss 001, then this patch series won't progress.

-- 
Justin

---

From: Tom Lane <[email-masked]>
Date: Sat, 05 Nov 2022 10:43:07 -0400

> wants to discuss 001, then this patch series won't progress.

Well ...

1. 0001 invents a new GUC but provides no documentation for it.
That certainly isn't committable, and it's discouraging the
discussion you seek because people have to read the whole patch
in detail to understand what is being proposed.

2. The same complaint for 0004, which labors under the additional
problem that MACHINE is one of the worst option names I've ever
seen proposed around here.  It conveys *nothing* about what it does
or is good for.  The fact that it's got no relationship to the GUC
name, and yet they're intimately connected, doesn't improve my
opinion of it.

3. I'm not really on board with the entire approach.  Making
EXPLAIN work significantly differently for developers and test
critters than it does for everyone else seems likely to promote
confusion and hide bugs.  I don't think getting rid of the need
for filter functions in test scripts is worth that.

4. I don't see how the current patches could pass under "make
installcheck" --- it looks to me like it only takes care of
applying the GUC change in installations built by pg_regress
or Cluster.pm.  To make this actually work, you'd have to
add "explain_regress = on" to the ALTER DATABASE SET commands
issued for regression databases.  That'd substantially increase
the problem of "it works differently than what users see", at
least for me --- I do a lot of stuff interactively in the
regression database.

5. The change in postgres_fdw.c in 0001 concerns me too.
Yeah, it will fix things if an updated postgres_fdw talks to
an updated server, but what about an older postgres_fdw?
Don't really want to see that case break; communication with
servers of different major versions is one of postgres_fdw's
main goals.

As a side note, 0001 also creates a hard break in postgres_fdw's
ability to work with pre-9.0 remote servers, which won't accept
"EXPLAIN (COSTS)".  Maybe we don't care about that anymore, given
the policy we've adopted elsewhere that we won't test or support
interoperability with versions before 9.2.  But we should either
make the command spelling conditional on remote version, or
officially move that goalpost.

			regards, tom lane

---

From: Justin Pryzby <[email-masked]>
Date: Thu, 1 Dec 2022 12:08:56 -0600

> 2. The same complaint for 0004, which labors under the additional

I suggested that the discussion be limited to the early patches (004 is
an optional, possible idea that I had, but it's evidently still causing
confusion).

> for filter functions in test scripts is worth that.

I'm open to suggestions how else to move towards the goal.

Note that there's a few other things which have vaguely-similar
behavior:

HIDE_TABLEAM=on
HIDE_TOAST_COMPRESSION=on
compute_query_id = regress

Another possibility is to make a new "explain" format, like
| explain(FORMAT REGRESS, ...).

...but I don't see how that's would be less objectionable than what I've
written.

The patch record should probably be closed until someone proposes
another way to implement what's necessary to enable explain(BUFFERS) by
default.

-- 
Justin