On Tue, Oct 25, 2022 at 03:49:14PM +0200, Laurenz Albe wrote:
> On Thu, 2022-10-20 at 21:09 -0500, Justin Pryzby wrote:
> > Rebased.
>
> I had a look at the patch set.
Thanks for looking
> @@ -2288,6 +2288,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)
> {
>
> 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.
> 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?
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.
> > 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?
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.
> This is not enough. The patch would have to update all the examples
> 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 c4527a8d878cfe48d2b62f6f631adbdf37ea83c2 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
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_tables.c | 13 +++++++++++++
src/include/commands/explain.h | 2 ++
src/test/perl/PostgreSQL/Test/Cluster.pm | 1 +
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 | 1 +
src/test/regress/sql/explain.sql | 4 ++++
src/test/regress/sql/inherit.sql | 2 +-
src/test/regress/sql/stats_ext.sql | 2 +-
12 files changed, 40 insertions(+), 7 deletions(-)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 8d7500abfbd..3a4f56695b1 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3138,7 +3138,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 f86983c6601..373fde4d498 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_tables.c b/src/backend/utils/misc/guc_tables.c
index 05ab087934c..1d34e6bdb7b 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -36,6 +36,7 @@
#include "catalog/namespace.h"
#include "catalog/storage.h"
#include "commands/async.h"
+#include "commands/explain.h"
#include "commands/tablespace.h"
#include "commands/trigger.h"
#include "commands/user.h"
@@ -967,6 +968,18 @@ 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
+ },
+
{
{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Enables genetic query optimization."),
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index 9ebde089aed..efcb14265c6 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 PGDLLIMPORT 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/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index d80134b26f3..e21fa56ca01 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -488,6 +488,7 @@ sub init
print $conf "log_statement = all\n";
print $conf "log_replication_commands = on\n";
print $conf "wal_retrieve_retry_interval = '500ms'\n";
+ print $conf "explain_regress = on\n";
# If a setting tends to affect whether tests pass or fail, print it after
# TEMP_CONFIG. Otherwise, print it before TEMP_CONFIG, thereby permitting
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 48620edbc2b..4abc5a346c6 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 a2bc409e06f..4b8f93ccf65 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 dda076847a3..10436f7f11a 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -2288,6 +2288,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 = on\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 ae3f7a308d7..dbb3799d5e2 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 19417561bd6..5bd6b9a41ab 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.25.1
>From 6294293f8205d4bb85ed9103c25f4b9cf73ed987 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
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 c109d97635b..e124a20f250 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 b79fe9a1c0e..03f2e9e158b 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 f133b5a4ac7..5f639a461fd 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 689c448cc20..85bfb2bf163 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.25.1
>From eec34ddd02c8be8aa6dbebd8328cc5ced4bf2bb9 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Wed, 22 Jul 2020 19:20:40 -0500
Subject: [PATCH 3/7] Make explain default to BUFFERS TRUE
On Tue, Oct 25, 2022 at 03:49:14PM +0200, Laurenz Albe wrote:
> This is not enough. The patch would have to update all the examples that use EXPLAIN ANALYZE.
git grep -i 'explain.*analyze' doc
---
contrib/auto_explain/auto_explain.c | 4 ++--
doc/src/sgml/auto-explain.sgml | 2 +-
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 ++++++++
6 files changed, 15 insertions(+), 7 deletions(-)
diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 269a0fa86c5..adcb03c73fb 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -29,7 +29,7 @@ static int auto_explain_log_min_duration = -1; /* msec or -1 */
static int auto_explain_log_parameter_max_length = -1; /* bytes or -1 */
static bool auto_explain_log_analyze = false;
static bool auto_explain_log_verbose = false;
-static bool auto_explain_log_buffers = false;
+static bool auto_explain_log_buffers = true;
static bool auto_explain_log_wal = false;
static bool auto_explain_log_triggers = false;
static bool auto_explain_log_timing = true;
@@ -154,7 +154,7 @@ _PG_init(void)
"Log buffers usage.",
NULL,
&auto_explain_log_buffers,
- false,
+ true,
PGC_SUSET,
0,
NULL,
diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml
index 394fec94e88..a5aa9c0cf7b 100644
--- a/doc/src/sgml/auto-explain.sgml
+++ b/doc/src/sgml/auto-explain.sgml
@@ -122,7 +122,7 @@ LOAD 'auto_explain';
equivalent to the <literal>BUFFERS</literal> option of <command>EXPLAIN</command>.
This parameter has no effect
unless <varname>auto_explain.log_analyze</varname> is enabled.
- This parameter is off by default.
+ This parameter is on by default.
Only superusers can change this setting.
</para>
</listitem>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 559eb898a9a..78ff44b4853 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7994,7 +7994,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 c3ee47b3d6d..a7bcc3f3fa9 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 @> 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 < 100 AND unique2 > 9000;
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index d4895b9d7d4..8a7435789b3 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -191,7 +191,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 373fde4d498..7880e18ff67 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.25.1
>From 9b7393ca64c77580a1db7b7d09f662429e3b51c7 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
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.
---
src/backend/commands/explain.c | 76 +++++++++++++------
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/merge.out | 22 +++---
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 | 16 ++++
src/test/regress/sql/memoize.sql | 4 +-
src/test/regress/sql/select_parallel.sql | 20 +----
src/test/regress/sql/subselect.sql | 19 +----
15 files changed, 192 insertions(+), 159 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 7880e18ff67..36ba7988d62 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);
@@ -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,
}
/* Show buffer usage in planning */
- if (bufusage)
+ if (bufusage && es->machine)
{
ExplainOpenGroup("Planning", "Planning", true, es);
show_buffer_usage(es, bufusage, true);
@@ -1803,7 +1812,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;
@@ -2785,8 +2794,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
{
@@ -2830,8 +2843,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
{
@@ -3119,25 +3136,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');
}
}
}
@@ -3221,12 +3239,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');
}
}
@@ -3295,6 +3317,10 @@ 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;
@@ -3413,6 +3439,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 efcb14265c6..35367137a3d 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 4abc5a346c6..0e2cc7cc4f6 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -291,6 +291,61 @@ select explain_filter('explain (analyze, buffers, format json) select * from int
(1 row)
set track_io_timing = off;
+-- 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 0a631124c22..078f10acfd0 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 00438eb1ea0..1b1557ce9fc 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,
@@ -111,11 +107,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);
@@ -129,15 +124,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('
@@ -146,15 +139,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
@@ -176,7 +167,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)
@@ -191,7 +182,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/merge.out b/src/test/regress/expected/merge.out
index 787af41dfe5..4f4b0954bb9 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1354,11 +1354,11 @@ WHEN MATCHED THEN
Merge Cond: (t.a = s.a)
-> Sort (actual rows=50 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_mtarget t (actual rows=50 loops=1)
-> Sort (actual rows=100 loops=1)
Sort Key: s.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_msource s (actual rows=100 loops=1)
(12 rows)
@@ -1375,11 +1375,11 @@ WHEN MATCHED AND t.a < 10 THEN
Merge Cond: (t.a = s.a)
-> Sort (actual rows=50 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_mtarget t (actual rows=50 loops=1)
-> Sort (actual rows=100 loops=1)
Sort Key: s.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_msource s (actual rows=100 loops=1)
(12 rows)
@@ -1398,11 +1398,11 @@ WHEN MATCHED AND t.a >= 10 AND t.a <= 20 THEN
Merge Cond: (t.a = s.a)
-> Sort (actual rows=50 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_mtarget t (actual rows=50 loops=1)
-> Sort (actual rows=100 loops=1)
Sort Key: s.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_msource s (actual rows=100 loops=1)
(12 rows)
@@ -1419,11 +1419,11 @@ WHEN NOT MATCHED AND s.a < 10 THEN
Merge Cond: (s.a = t.a)
-> Sort (actual rows=100 loops=1)
Sort Key: s.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_msource s (actual rows=100 loops=1)
-> Sort (actual rows=45 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_mtarget t (actual rows=45 loops=1)
(12 rows)
@@ -1444,11 +1444,11 @@ WHEN NOT MATCHED AND s.a < 20 THEN
Merge Cond: (s.a = t.a)
-> Sort (actual rows=100 loops=1)
Sort Key: s.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_msource s (actual rows=100 loops=1)
-> Sort (actual rows=49 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_mtarget t (actual rows=49 loops=1)
(12 rows)
@@ -1464,7 +1464,7 @@ WHEN MATCHED AND t.a < 10 THEN
Merge Cond: (t.a = s.a)
-> Sort (actual rows=0 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
-> Seq Scan on ex_mtarget t (actual rows=0 loops=1)
Filter: (a < '-1000'::integer)
Rows Removed by Filter: 54
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 91f74fe47a3..b285ed5ecb1 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 63d26d44fc3..c6c29ec2bd5 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1619,27 +1619,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)
@@ -1651,7 +1639,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 dbb3799d5e2..d7207209d51 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -79,6 +79,22 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8'
set track_io_timing = on;
select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8');
set track_io_timing = off;
+-- 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,
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index 0979bcdf768..5d3e37f92de 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 62fb68c7a04..097c64edd50 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 40276708c99..09ab3f2a3e9 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -838,26 +838,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.25.1
>From 32643b6d32e9a9fbe5dd543837a99dbd055c6d00 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 16 Nov 2021 11:22:40 -0600
Subject: [PATCH 5/7] ..and 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/merge.out | 3 +-
src/test/regress/expected/partition_prune.out | 29 +++++----------
src/test/regress/expected/select_parallel.out | 4 +--
src/test/regress/sql/partition_prune.sql | 1 -
7 files changed, 33 insertions(+), 55 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 558e94b8450..d8787b2ec18 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10915,13 +10915,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: (a = local_tbl.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
@@ -11203,8 +11202,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 36ba7988d62..e2666a990a0 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1796,7 +1796,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;
@@ -1809,7 +1809,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)
@@ -1827,7 +1827,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)
@@ -1845,7 +1845,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;
@@ -1854,7 +1854,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,
@@ -1882,7 +1882,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,
@@ -1920,7 +1920,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;
@@ -1934,7 +1934,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;
@@ -1950,7 +1950,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);
}
@@ -1967,14 +1967,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);
@@ -1984,7 +1984,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)
@@ -1998,7 +1998,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;
@@ -2011,7 +2011,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;
@@ -2024,7 +2024,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;
@@ -2032,7 +2032,7 @@ 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;
@@ -2047,7 +2047,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
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;
@@ -2069,7 +2069,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 1b1557ce9fc..7f4b73fd42d 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,
@@ -103,14 +101,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/merge.out b/src/test/regress/expected/merge.out
index 4f4b0954bb9..1c0a453a981 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1467,11 +1467,10 @@ WHEN MATCHED AND t.a < 10 THEN
Sort Method: quicksort
-> Seq Scan on ex_mtarget t (actual rows=0 loops=1)
Filter: (a < '-1000'::integer)
- Rows Removed by Filter: 54
-> Sort (never executed)
Sort Key: s.a
-> Seq Scan on ex_msource s (never executed)
-(12 rows)
+(11 rows)
DROP TABLE ex_msource, ex_mtarget;
DROP FUNCTION explain_merge(text);
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 b285ed5ecb1..aa6e385d84d 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.25.1
>From 5281dc5c5e0d88058b939816c0013a33dad1eea6 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 16 Nov 2021 10:37:45 -0600
Subject: [PATCH 6/7] ..and 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 e2666a990a0..26d37b2309b 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1864,7 +1864,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;
@@ -1892,7 +1892,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 aa6e385d84d..c321d02e6d5 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.25.1
>From 6312b6fbd637d918c2a95c5df9fb09b87751cabf Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 16 Nov 2021 10:51:39 -0600
Subject: [PATCH 7/7] ..and 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 26d37b2309b..b1e210c2183 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1676,6 +1676,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);
@@ -1704,8 +1705,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);
+ }
}
}
@@ -1741,7 +1746,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);
@@ -1755,7 +1760,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.25.1
