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/20200306213310.gm...@telsasoft.com
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 6804bc6945f7c7e0d51beb1930b80cf2ee0e3f82 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 22 Feb 2020 21:17:10 -0600
Subject: [PATCH 01/11] 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 fa9a099f13..5cd90d7404 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 09f5253abb..e5362fb560 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 a292498e2f..9f791a01bb 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/trigger.h"
#include "commands/user.h"
@@ -1471,6 +1472,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 e94d9e49cf..d849bae451 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 1734dfee8c..188dc7ccec 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 2d49e765de..38fb5f94c6 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 c60ba45aba..f7f516ea1c 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 2c8a600bad..255531580e 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -801,7 +801,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;
@@ -2469,6 +2469,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 84549c78fa..f91d1004d5 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 195aedb5ff..868ee58b80 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 6fb37962a7..c544e54a35 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.0
>From f7758a9b51f424c089354a4004795d9187d87a45 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 15 Nov 2021 21:54:12 -0600
Subject: [PATCH 02/11] 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 313c72a268..e7ce5bb188 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 43b8209d22..3de0be2a15 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 13c3c360c2..de93145bf0 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 68b9ccfd45..e0b562933d 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 7e903c339a..7031e86a2c 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 313e0fb9b6..3d1f447088 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.0
>From d851c4348dfe88147d977977a5a19e592f02e94b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Wed, 22 Jul 2020 19:20:40 -0500
Subject: [PATCH 03/11] Make explain default to BUFFERS TRUE
---
contrib/auto_explain/auto_explain.c | 4 ++--
doc/src/sgml/config.sgml | 4 ++--
doc/src/sgml/perform.sgml | 1 +
doc/src/sgml/ref/explain.sgml | 2 +-
src/backend/commands/explain.c | 8 ++++++++
5 files changed, 14 insertions(+), 5 deletions(-)
diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 59ba63455f..91f59124b8 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 ab617c7b86..a3f1511e12 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7615,8 +7615,8 @@ 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, by autovacuum for auto-vacuums and auto-analyzes, when
- <xref linkend="guc-log-autovacuum-min-duration"/> is set and by
+ is enabled, by autovacuum for auto-vacuums and auto-analyzes, when
+ <xref linkend="guc-log-autovacuum-min-duration"/> is set, and by
<xref linkend="pgstatstatements"/>. Only superusers can change this
setting.
</para>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e..16383b8f5f 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -731,6 +731,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
</para>
<para>
+XXX
<command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with
<literal>ANALYZE</literal> to get even more run time statistics:
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 4d758fb237..ceb0f4c83a 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 e5362fb560..050b7467c6 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.0
>From 2573d706e8985221d2795194bad3060720afa765 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 22 Feb 2020 18:45:22 -0600
Subject: [PATCH 04/11] 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 050b7467c6..833daac940 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 d849bae451..56817e31ef 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 4150b2dee6..ee3e495a64 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 d5239ff228..082205d36b 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 188dc7ccec..36c20e0680 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 545e301e48..56a32df536 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 4ca0bd1f1e..fbe6bed433 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 7555764c77..cabadd48b8 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 4ea1aa7dfd..9f36c62741 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 4e8ddc7061..199badece3 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1527,27 +1527,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)
@@ -1559,7 +1547,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 f91d1004d5..c38857d346 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 c6ed5a2aa6..1368f8dd18 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 f924731248..fc58616123 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 e879999708..82285250e7 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -797,26 +797,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.0
>From 093713b6a792fad4a85affb6e612117002d939ad Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 16 Nov 2021 11:22:40 -0600
Subject: [PATCH 05/11] 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 5196e4797a..43f0b98432 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10493,13 +10493,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
@@ -10633,8 +10632,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 833daac940..9ef21d1044 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 fbe6bed433..3e521f3487 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 cabadd48b8..3576e65bc2 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 9f36c62741..02176d7e2f 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 d70bd8610c..e3938bea9c 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.0
