On Fri, Nov 12, 2021 at 02:58:07PM -0800, Nikolay Samokhvalov wrote:
> Re-reading the thread [1] (cannot answer there – don't have those emails in
> my box anymore), I see that there was strong support for enabling BUFFERS
> in EXPLAIN ANALYZE by default. And there were patches. Commitfest entry [2]
> was marked Rejected because there were questions to the implementation
> based on GUCs.
I think the only reason this isn't already the default is because of (3):
> 3. How to adjust regression tests? Of course, now many tests fail. Same
> question as for documentation. Excluding buffer, numbers would be an easier
> fix, of course – but at least some tests should
> check the behavior of BUFFERS (such as both default options – with and
> without ANALYZE).
Some time ago, I had a few relevant patches:
1) add explain(REGRESS) which is shorthand for (BUFFERS OFF, TIMING OFF, COSTS
OFF, SUMMARY OFF)
2) add explain(MACHINE) which elides machine-specific output from explain;
for example, Heap Fetches, sort spaceUsed, hash nbuckets, and tidbitmap
stuff.
https://www.postgresql.org/message-id/flat/20200306213310.gm...@telsasoft.com
> 1. Should BUFFERS be enabled for regular (w/o ANALYZE) EXPLAIN? Now it may
> make sense because of the buffer numbers the planner uses. This patch
> doesn't do that, but it definitely may make sense because it can help
> people understand why planning time is so big, in some cases.
I think it *should* be enabled for planning, since that makes the default
easier to understand and document, and it makes a user's use of "explain"
easier.
However, that makes this patch series more complicated: explain(ANALYZE) is
rare in the regression tests, but this would affect the output of bare
"explain", which affects almost every test.
I think the answer to that is to add a GUC (as Tom suggested in an old thread)
like explain_regress=on, which causes explain to omit these details. This
would be instead of my explain(REGRESS), and would change the defaults of
various params in a central place, to avoid the need to update many regression
tests.
In the future, this might also handle the stuff that my "explain(MACHINE)"
attempted to handle.
I've rebased my patches like this. I think the explain(REGRESS) should be
re-written as a GUC which is set by regress.c. I'm interested to hear from a
reviewer if this is worth pursing like this.
--
Justin
>From cdf439d9ccdec0f26bdd6b86f9f27bd4e55e5b9e 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/3] Add explain(REGRESS)...
This is a convenience shorthand for: costs off, timing off, summary off.
It'd be reasonable to use this for new regression tests which are not intended
to be backpatched.
---
src/backend/commands/explain.c | 21 +++++++++++++++++--
src/test/regress/expected/select.out | 2 +-
src/test/regress/expected/select_parallel.out | 2 +-
src/test/regress/sql/select.sql | 2 +-
src/test/regress/sql/select_parallel.sql | 2 +-
5 files changed, 23 insertions(+), 6 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 10644dfac4..673ad7651f 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -170,8 +170,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
Query *query;
List *rewritten;
ListCell *lc;
+ bool regress = false; /* A convenience shortcut to other options */
bool timing_set = false;
bool summary_set = false;
+ bool costs_set = false;
/* Parse options list. */
foreach(lc, stmt->options)
@@ -183,13 +185,19 @@ 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)
es->wal = defGetBoolean(opt);
else if (strcmp(opt->defname, "settings") == 0)
es->settings = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "regress") == 0)
+ regress = defGetBoolean(opt);
else if (strcmp(opt->defname, "timing") == 0)
{
timing_set = true;
@@ -227,13 +235,22 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
parser_errposition(pstate, opt->location)));
}
+ if (regress && (es->timing || es->summary ||
+ (costs_set && es->costs))) // XXX
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN option REGRESS cannot be specified with any of: TIMING, SUMMARY, COSTS")));
+
+ /* if the costs option was not set explicitly, set default value */
+ es->costs = (costs_set) ? es->costs : es->costs && !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 && !regress;
/* check that timing is used with EXPLAIN ANALYZE */
if (es->timing && !es->analyze)
@@ -242,7 +259,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 && !regress;
query = castNode(Query, stmt->query);
if (IsQueryIdEnabled())
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index c441049f41..2a94111282 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -753,7 +753,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
(1 row)
-- actually run the query with an analyze to use the partial index
-explain (costs off, analyze on, timing off, summary off)
+explain (analyze, regress)
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
QUERY PLAN
-----------------------------------------------------------------
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 4ea1aa7dfd..e3facf4a61 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -542,7 +542,7 @@ select count(*) from bmscantest where a>1;
-- test accumulation of stats for parallel nodes
reset enable_seqscan;
alter table tenk2 set (parallel_workers = 0);
-explain (analyze, timing off, summary off, costs off)
+explain (analyze, regress)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1
and tenk2.thousand=0;
QUERY PLAN
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index b5929b2eca..1c757fe0bf 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -196,7 +196,7 @@ explain (costs off)
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
-- actually run the query with an analyze to use the partial index
-explain (costs off, analyze on, timing off, summary off)
+explain (analyze, regress)
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
explain (costs off)
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index f924731248..24d688ffc8 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -215,7 +215,7 @@ select count(*) from bmscantest where a>1;
-- test accumulation of stats for parallel nodes
reset enable_seqscan;
alter table tenk2 set (parallel_workers = 0);
-explain (analyze, timing off, summary off, costs off)
+explain (analyze, regress)
select count(*) from tenk1, tenk2 where tenk1.hundred > 1
and tenk2.thousand=0;
alter table tenk2 reset (parallel_workers);
--
2.17.0
>From acd6c2065f7057f81ddf9efddfec7230e6b395dc Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Wed, 22 Jul 2020 19:20:40 -0500
Subject: [PATCH 2/3] Make explain analyze default to BUFFERS TRUE..
Opened question: should autoexplain too ?
---
contrib/auto_explain/auto_explain.c | 4 +-
doc/src/sgml/config.sgml | 4 +-
doc/src/sgml/perform.sgml | 1 +
doc/src/sgml/ref/explain.sgml | 5 +-
src/backend/commands/explain.c | 7 ++
.../regress/expected/incremental_sort.out | 26 ++++-
src/test/regress/expected/memoize.out | 2 +-
src/test/regress/expected/partition_prune.out | 96 +++++++++----------
src/test/regress/expected/select_parallel.out | 4 +-
src/test/regress/expected/subselect.out | 2 +-
src/test/regress/expected/tidscan.out | 6 +-
src/test/regress/sql/incremental_sort.sql | 2 +-
src/test/regress/sql/memoize.sql | 2 +-
src/test/regress/sql/partition_prune.sql | 96 +++++++++----------
src/test/regress/sql/select_parallel.sql | 4 +-
src/test/regress/sql/subselect.sql | 2 +-
src/test/regress/sql/tidscan.sql | 6 +-
17 files changed, 149 insertions(+), 120 deletions(-)
diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index e9092ba359..44ddee89da 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -26,7 +26,7 @@ PG_MODULE_MAGIC;
static int auto_explain_log_min_duration = -1; /* msec or -1 */
static bool auto_explain_log_analyze = false;
static bool auto_explain_log_verbose = false;
-static bool auto_explain_log_buffers = false;
+static bool auto_explain_log_buffers = false; // XXX
static bool auto_explain_log_wal = false;
static bool auto_explain_log_triggers = false;
static bool auto_explain_log_timing = true;
@@ -144,7 +144,7 @@ _PG_init(void)
&auto_explain_log_buffers,
false,
PGC_SUSET,
- 0,
+ 0, // XXX
NULL,
NULL,
NULL);
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3f806740d5..9a78f62434 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7612,8 +7612,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
displayed in <link linkend="monitoring-pg-stat-database-view">
<structname>pg_stat_database</structname></link>, in the output of
<xref linkend="sql-explain"/> when the <literal>BUFFERS</literal> option
- is used, by autovacuum for auto-vacuums and auto-analyzes, when
- <xref linkend="guc-log-autovacuum-min-duration"/> is set and by
+ is enabled, by autovacuum for auto-vacuums and auto-analyzes, when
+ <xref linkend="guc-log-autovacuum-min-duration"/> is set, and by
<xref linkend="pgstatstatements"/>. Only superusers can change this
setting.
</para>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e..16383b8f5f 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -731,6 +731,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
</para>
<para>
+XXX
<command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with
<literal>ANALYZE</literal> to get even more run time statistics:
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 4d758fb237..8e9e834771 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -189,8 +189,9 @@ ROLLBACK;
query processing.
The number of blocks shown for an
upper-level node includes those used by all its child nodes. In text
- format, only non-zero values are printed. It defaults to
- <literal>FALSE</literal>.
+ format, only non-zero values are printed. This parameter may only be
+ used when <literal>ANALYZE</literal> is also enabled. It defaults to
+ <literal>TRUE</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 673ad7651f..c5ade3554e 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)
@@ -244,6 +248,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
/* if the costs option was not set explicitly, set default value */
es->costs = (costs_set) ? es->costs : es->costs && !regress;
+ /* if the buffers option was not set explicitly, set default value */
+ es->buffers = (buffers_set) ? es->buffers : es->analyze && !regress;
+
if (es->wal && !es->analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 545e301e48..43de2745cf 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -52,7 +52,7 @@ declare
line text;
begin
for line in
- execute 'explain (analyze, costs off, summary off, timing off) ' || query
+ execute 'explain (analyze, regress) ' || query
loop
out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g');
return next;
@@ -574,7 +574,17 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
"Average Sort Space Used": "NN"+
} +
}, +
- "Parent Relationship": "Outer" +
+ "Local Hit Blocks": 0, +
+ "Temp Read Blocks": 0, +
+ "Local Read Blocks": 0, +
+ "Shared Hit Blocks": 9, +
+ "Shared Read Blocks": 0, +
+ "Parent Relationship": "Outer", +
+ "Temp Written Blocks": 0, +
+ "Local Dirtied Blocks": 0, +
+ "Local Written Blocks": 0, +
+ "Shared Dirtied Blocks": 0, +
+ "Shared Written Blocks": 0 +
} +
]
(1 row)
@@ -776,6 +786,9 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
"Average Sort Space Used": "NN"+
} +
}, +
+ "Local Hit Blocks": 0, +
+ "Temp Read Blocks": 0, +
+ "Local Read Blocks": 0, +
"Pre-sorted Groups": { +
"Group Count": 5, +
"Sort Methods Used": [ +
@@ -787,7 +800,14 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from
"Average Sort Space Used": "NN"+
} +
}, +
- "Parent Relationship": "Outer" +
+ "Shared Hit Blocks": 14, +
+ "Shared Read Blocks": 0, +
+ "Parent Relationship": "Outer", +
+ "Temp Written Blocks": 0, +
+ "Local Dirtied Blocks": 0, +
+ "Local Written Blocks": 0, +
+ "Shared Dirtied Blocks": 0, +
+ "Shared Written Blocks": 0 +
} +
]
(1 row)
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 9a025c4a7a..0a10c97894 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -10,7 +10,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
query)
loop
if hide_hitmiss = true then
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c77..111f5fc3a6 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1766,7 +1766,7 @@ create table ab_a3_b3 partition of ab_a3 for values in (3);
set enable_indexonlyscan = off;
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
+explain (analyze, regress) execute ab_q1 (2, 2, 3);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1779,7 +1779,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
(8 rows)
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+explain (analyze, regress) execute ab_q1 (1, 2, 3);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1802,7 +1802,7 @@ deallocate ab_q1;
-- Runtime pruning after optimizer pruning
prepare ab_q1 (int, int) as
select a from ab where a between $1 and $2 and b < 3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
+explain (analyze, regress) execute ab_q1 (2, 2);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1813,7 +1813,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
(6 rows)
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+explain (analyze, regress) execute ab_q1 (2, 4);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1832,7 +1832,7 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
-- different levels of partitioning.
prepare ab_q2 (int, int) as
select a from ab where a between $1 and $2 and b < (select 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+explain (analyze, regress) execute ab_q2 (2, 2);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1891,7 +1891,7 @@ begin;
-- Test run-time pruning using stable functions
create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
-- Ensure pruning works using a stable function containing no Vars
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
+explain (analyze, regress) select * from list_part where a = list_part_fn(1);
QUERY PLAN
------------------------------------------------------------------
Append (actual rows=1 loops=1)
@@ -1901,7 +1901,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
(4 rows)
-- Ensure pruning does not take place when the function has a Var parameter
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
+explain (analyze, regress) select * from list_part where a = list_part_fn(a);
QUERY PLAN
------------------------------------------------------------------
Append (actual rows=4 loops=1)
@@ -1916,7 +1916,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
(9 rows)
-- Ensure pruning does not take place when the expression contains a Var.
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
+explain (analyze, regress) select * from list_part where a = list_part_fn(1) + a;
QUERY PLAN
------------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -1952,7 +1952,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
$1)
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
@@ -2260,7 +2260,7 @@ reset parallel_tuple_cost;
reset min_parallel_table_scan_size;
reset max_parallel_workers_per_gather;
-- Test run-time partition pruning with an initplan
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
QUERY PLAN
-------------------------------------------------------------------------
@@ -2319,7 +2319,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1
(52 rows)
-- Test run-time partition pruning with UNION ALL parents
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
QUERY PLAN
-------------------------------------------------------------------------------
@@ -2363,7 +2363,7 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
(37 rows)
-- A case containing a UNION ALL with a non-partitioned child.
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
QUERY PLAN
-------------------------------------------------------------------------------
@@ -2422,7 +2422,7 @@ union all
select tableoid::regclass,a,b from ab
) ab where a = $1 and b = (select -10);
-- Ensure the xy_1 subplan is not pruned.
-explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
+explain (analyze, regress) execute ab_q6(1);
QUERY PLAN
--------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2463,7 +2463,7 @@ deallocate ab_q5;
deallocate ab_q6;
-- UPDATE on a partition subtree has been seen to have problems.
insert into ab values (1,2);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
QUERY PLAN
-------------------------------------------------------------------------------------------
@@ -2512,7 +2512,7 @@ table ab;
-- Test UPDATE where source relation has run-time pruning enabled
truncate ab;
insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
QUERY PLAN
------------------------------------------------------------------------------
@@ -2567,7 +2567,7 @@ create index tprt6_idx on tprt_6 (col1);
insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
set enable_hashjoin = off;
set enable_mergejoin = off;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2588,7 +2588,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
Index Cond: (col1 < tbl1.col1)
(15 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2633,7 +2633,7 @@ order by tbl1.col1, tprt.col1;
-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2654,7 +2654,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
Index Cond: (col1 < tbl1.col1)
(15 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2718,7 +2718,7 @@ order by tbl1.col1, tprt.col1;
-- Last partition
delete from tbl1;
insert into tbl1 values (4400);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------
@@ -2750,7 +2750,7 @@ order by tbl1.col1, tprt.col1;
-- No matching partition
delete from tbl1;
insert into tbl1 values (10000);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
-------------------------------------------------------------------
@@ -2790,7 +2790,7 @@ alter table part_cab attach partition part_abc_p1 for values in(3);
prepare part_abc_q1 (int, int, int) as
select * from part_abc where a = $1 and b = $2 and c = $3;
-- Single partition should be scanned.
-explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+explain (analyze, regress) execute part_abc_q1 (1, 2, 3);
QUERY PLAN
----------------------------------------------------------
Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1)
@@ -2815,7 +2815,7 @@ select * from listp where b = 1;
-- partitions before finally detecting the correct set of 2nd level partitions
-- which match the given parameter.
prepare q1 (int,int) as select * from listp where b in ($1,$2);
-explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
+explain (analyze, regress) execute q1 (1,1);
QUERY PLAN
-------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2824,7 +2824,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
Filter: (b = ANY (ARRAY[$1, $2]))
(4 rows)
-explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
+explain (analyze, regress) execute q1 (2,2);
QUERY PLAN
-------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2834,7 +2834,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
(4 rows)
-- Try with no matching partitions.
-explain (analyze, costs off, summary off, timing off) execute q1 (0,0);
+explain (analyze, regress) execute q1 (0,0);
QUERY PLAN
--------------------------------
Append (actual rows=0 loops=1)
@@ -2845,7 +2845,7 @@ deallocate q1;
-- Test more complex cases where a not-equal condition further eliminates partitions.
prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
-- Both partitions allowed by IN clause, but one disallowed by <> clause
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
+explain (analyze, regress) execute q1 (1,2,2,0);
QUERY PLAN
-------------------------------------------------------------------------
Append (actual rows=0 loops=1)
@@ -2855,7 +2855,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
(4 rows)
-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
+explain (analyze, regress) execute q1 (1,2,2,1);
QUERY PLAN
--------------------------------
Append (actual rows=0 loops=1)
@@ -2863,7 +2863,7 @@ explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
(2 rows)
-- Ensure Params that evaluate to NULL properly prune away all partitions
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select null::int);
QUERY PLAN
------------------------------------------------------
@@ -2888,7 +2888,7 @@ create table stable_qual_pruning2 partition of stable_qual_pruning
create table stable_qual_pruning3 partition of stable_qual_pruning
for values from ('3000-02-01') to ('3000-03-01');
-- comparison against a stable value requires run-time pruning
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < localtimestamp;
QUERY PLAN
--------------------------------------------------------------------------------------
@@ -2901,7 +2901,7 @@ select * from stable_qual_pruning where a < localtimestamp;
(6 rows)
-- timestamp < timestamptz comparison is only stable, not immutable
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
QUERY PLAN
--------------------------------------------------------------------------------------
@@ -2912,7 +2912,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
(4 rows)
-- check ScalarArrayOp cases
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
QUERY PLAN
@@ -2921,7 +2921,7 @@ select * from stable_qual_pruning
One-Time Filter: false
(2 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
QUERY PLAN
@@ -2930,7 +2930,7 @@ select * from stable_qual_pruning
Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[]))
(2 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
QUERY PLAN
@@ -2941,7 +2941,7 @@ select * from stable_qual_pruning
Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP]))
(4 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
QUERY PLAN
@@ -2950,7 +2950,7 @@ select * from stable_qual_pruning
Subplans Removed: 3
(2 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
QUERY PLAN
@@ -2961,7 +2961,7 @@ select * from stable_qual_pruning
Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[]))
(4 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(null::timestamptz[]);
QUERY PLAN
@@ -2989,7 +2989,7 @@ create table mc3p1 partition of mc3p
create table mc3p2 partition of mc3p
for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from mc3p where a < 3 and abs(b) = 1;
QUERY PLAN
--------------------------------------------------------
@@ -3009,7 +3009,7 @@ select * from mc3p where a < 3 and abs(b) = 1;
--
prepare ps1 as
select * from mc3p where a = $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps1(1);
QUERY PLAN
--------------------------------------------------------
@@ -3024,7 +3024,7 @@ execute ps1(1);
deallocate ps1;
prepare ps2 as
select * from mc3p where a <= $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps2(1);
QUERY PLAN
--------------------------------------------------------
@@ -3046,7 +3046,7 @@ insert into boolvalues values('t'),('f');
create table boolp (a bool) partition by list (a);
create table boolp_t partition of boolp for values in('t');
create table boolp_f partition of boolp for values in('f');
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where value);
QUERY PLAN
-----------------------------------------------------------
@@ -3061,7 +3061,7 @@ select * from boolp where a = (select value from boolvalues where value);
Filter: (a = $0)
(9 rows)
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where not value);
QUERY PLAN
-----------------------------------------------------------
@@ -3090,7 +3090,7 @@ insert into ma_test select x,x from generate_series(0,29) t(x);
create index on ma_test (b);
analyze ma_test;
prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
-explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
+explain (analyze, regress) execute mt_q1(15);
QUERY PLAN
-----------------------------------------------------------------------------------------
Merge Append (actual rows=2 loops=1)
@@ -3111,7 +3111,7 @@ execute mt_q1(15);
25
(2 rows)
-explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
+explain (analyze, regress) execute mt_q1(25);
QUERY PLAN
-----------------------------------------------------------------------------------------
Merge Append (actual rows=1 loops=1)
@@ -3129,7 +3129,7 @@ execute mt_q1(25);
(1 row)
-- Ensure MergeAppend behaves correctly when no subplans match
-explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
+explain (analyze, regress) execute mt_q1(35);
QUERY PLAN
--------------------------------------
Merge Append (actual rows=0 loops=1)
@@ -3145,7 +3145,7 @@ execute mt_q1(35);
deallocate mt_q1;
prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
-- Ensure output list looks sane when the MergeAppend has no subplans.
-explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
+explain (analyze, verbose, regress) execute mt_q2 (35);
QUERY PLAN
--------------------------------------------
Limit (actual rows=0 loops=1)
@@ -3157,7 +3157,7 @@ explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35
deallocate mt_q2;
-- ensure initplan params properly prune partitions
-explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
+explain (analyze, regress) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Merge Append (actual rows=20 loops=1)
@@ -3607,7 +3607,7 @@ create table listp (a int, b int) partition by list (a);
create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2) partition by list(b);
create table listp2_10 partition of listp2 for values in (10);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select 2) and b <> 10;
QUERY PLAN
--------------------------------------------------
@@ -3738,7 +3738,7 @@ create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3);
create table rangep_100_to_200 partition of rangep for values from (100) to (200);
create index on rangep (a);
-- Ensure run-time pruning works on the nested Merge Append
-explain (analyze on, costs off, timing off, summary off)
+explain (analyze on, regress)
select * from rangep where b IN((select 1),(select 2)) order by a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index e3facf4a61..36b853213e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -568,7 +568,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, timing off, summary off, costs off)
+ explain (analyze, regress)
select * from
(select ten from tenk1 where ten < 100 order by ten) ss
right join (values (1),(2),(3)) v(x) on true
@@ -1043,7 +1043,7 @@ explain (costs off)
-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 1;
-EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
+EXPLAIN (analyze, regress) SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Gather (actual rows=10000 loops=1)
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 0742626033..ec75bd2132 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1531,7 +1531,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, summary off, timing off, costs off)
+ explain (analyze, regress)
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
loop
ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index 13c3c360c2..bbb8b97e95 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, REGRESS)
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, REGRESS)
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, REGRESS)
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/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql
index d8768a6b54..3d5231d3b7 100644
--- a/src/test/regress/sql/incremental_sort.sql
+++ b/src/test/regress/sql/incremental_sort.sql
@@ -26,7 +26,7 @@ declare
line text;
begin
for line in
- execute 'explain (analyze, costs off, summary off, timing off) ' || query
+ execute 'explain (analyze, regress) ' || query
loop
out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g');
return next;
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index 548cc3eee3..27c92bfd6c 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -11,7 +11,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
query)
loop
if hide_hitmiss = true then
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d70bd8610c..47d0bc25fc 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -375,8 +375,8 @@ set enable_indexonlyscan = off;
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+explain (analyze, regress) execute ab_q1 (2, 2, 3);
+explain (analyze, regress) execute ab_q1 (1, 2, 3);
deallocate ab_q1;
@@ -384,15 +384,15 @@ deallocate ab_q1;
prepare ab_q1 (int, int) as
select a from ab where a between $1 and $2 and b < 3;
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
-explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+explain (analyze, regress) execute ab_q1 (2, 2);
+explain (analyze, regress) execute ab_q1 (2, 4);
-- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at
-- different levels of partitioning.
prepare ab_q2 (int, int) as
select a from ab where a between $1 and $2 and b < (select 3);
-explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+explain (analyze, regress) execute ab_q2 (2, 2);
-- As above, but swap the PARAM_EXEC Param to the first partition level
prepare ab_q3 (int, int) as
@@ -429,13 +429,13 @@ begin;
create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
-- Ensure pruning works using a stable function containing no Vars
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
+explain (analyze, regress) select * from list_part where a = list_part_fn(1);
-- Ensure pruning does not take place when the function has a Var parameter
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
+explain (analyze, regress) select * from list_part where a = list_part_fn(a);
-- Ensure pruning does not take place when the expression contains a Var.
-explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
+explain (analyze, regress) select * from list_part where a = list_part_fn(1) + a;
rollback;
@@ -458,7 +458,7 @@ declare
ln text;
begin
for ln in
- execute format('explain (analyze, costs off, summary off, timing off) %s',
+ execute format('explain (analyze, regress) %s',
$1)
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
@@ -541,15 +541,15 @@ reset min_parallel_table_scan_size;
reset max_parallel_workers_per_gather;
-- Test run-time partition pruning with an initplan
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
-- Test run-time partition pruning with UNION ALL parents
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
-- A case containing a UNION ALL with a non-partitioned child.
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
@@ -569,7 +569,7 @@ union all
) ab where a = $1 and b = (select -10);
-- Ensure the xy_1 subplan is not pruned.
-explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
+explain (analyze, regress) execute ab_q6(1);
-- Ensure we see just the xy_1 row.
execute ab_q6(100);
@@ -586,14 +586,14 @@ deallocate ab_q6;
-- UPDATE on a partition subtree has been seen to have problems.
insert into ab values (1,2);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
table ab;
-- Test UPDATE where source relation has run-time pruning enabled
truncate ab;
insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
select tableoid::regclass, * from ab;
@@ -624,10 +624,10 @@ insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
set enable_hashjoin = off;
set enable_mergejoin = off;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -640,10 +640,10 @@ order by tbl1.col1, tprt.col1;
-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -657,7 +657,7 @@ order by tbl1.col1, tprt.col1;
-- Last partition
delete from tbl1;
insert into tbl1 values (4400);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -667,7 +667,7 @@ order by tbl1.col1, tprt.col1;
-- No matching partition
delete from tbl1;
insert into tbl1 values (10000);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
select tbl1.col1, tprt.col1 from tbl1
@@ -690,7 +690,7 @@ prepare part_abc_q1 (int, int, int) as
select * from part_abc where a = $1 and b = $2 and c = $3;
-- Single partition should be scanned.
-explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+explain (analyze, regress) execute part_abc_q1 (1, 2, 3);
deallocate part_abc_q1;
@@ -710,12 +710,12 @@ select * from listp where b = 1;
-- which match the given parameter.
prepare q1 (int,int) as select * from listp where b in ($1,$2);
-explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
+explain (analyze, regress) execute q1 (1,1);
-explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
+explain (analyze, regress) execute q1 (2,2);
-- Try with no matching partitions.
-explain (analyze, costs off, summary off, timing off) execute q1 (0,0);
+explain (analyze, regress) execute q1 (0,0);
deallocate q1;
@@ -723,13 +723,13 @@ deallocate q1;
prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
-- Both partitions allowed by IN clause, but one disallowed by <> clause
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
+explain (analyze, regress) execute q1 (1,2,2,0);
-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
-explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
+explain (analyze, regress) execute q1 (1,2,2,1);
-- Ensure Params that evaluate to NULL properly prune away all partitions
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select null::int);
drop table listp;
@@ -746,30 +746,30 @@ create table stable_qual_pruning3 partition of stable_qual_pruning
for values from ('3000-02-01') to ('3000-03-01');
-- comparison against a stable value requires run-time pruning
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < localtimestamp;
-- timestamp < timestamptz comparison is only stable, not immutable
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
-- check ScalarArrayOp cases
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from stable_qual_pruning
where a = any(null::timestamptz[]);
@@ -789,7 +789,7 @@ create table mc3p2 partition of mc3p
for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from mc3p where a < 3 and abs(b) = 1;
--
@@ -799,12 +799,12 @@ select * from mc3p where a < 3 and abs(b) = 1;
--
prepare ps1 as
select * from mc3p where a = $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps1(1);
deallocate ps1;
prepare ps2 as
select * from mc3p where a <= $1 and abs(b) < (select 3);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
execute ps2(1);
deallocate ps2;
@@ -818,10 +818,10 @@ create table boolp (a bool) partition by list (a);
create table boolp_t partition of boolp for values in('t');
create table boolp_f partition of boolp for values in('f');
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where value);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from boolp where a = (select value from boolvalues where not value);
drop table boolp;
@@ -841,12 +841,12 @@ create index on ma_test (b);
analyze ma_test;
prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
-explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
+explain (analyze, regress) execute mt_q1(15);
execute mt_q1(15);
-explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
+explain (analyze, regress) execute mt_q1(25);
execute mt_q1(25);
-- Ensure MergeAppend behaves correctly when no subplans match
-explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
+explain (analyze, regress) execute mt_q1(35);
execute mt_q1(35);
deallocate mt_q1;
@@ -854,12 +854,12 @@ deallocate mt_q1;
prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
-- Ensure output list looks sane when the MergeAppend has no subplans.
-explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
+explain (analyze, verbose, regress) execute mt_q2 (35);
deallocate mt_q2;
-- ensure initplan params properly prune partitions
-explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
+explain (analyze, regress) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
reset enable_seqscan;
reset enable_sort;
@@ -1039,7 +1039,7 @@ create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2) partition by list(b);
create table listp2_10 partition of listp2 for values in (10);
-explain (analyze, costs off, summary off, timing off)
+explain (analyze, regress)
select * from listp where a = (select 2) and b <> 10;
--
@@ -1107,7 +1107,7 @@ create table rangep_100_to_200 partition of rangep for values from (100) to (200
create index on rangep (a);
-- Ensure run-time pruning works on the nested Merge Append
-explain (analyze on, costs off, timing off, summary off)
+explain (analyze on, regress)
select * from rangep where b IN((select 1),(select 2)) order by a;
reset enable_sort;
drop table rangep;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 24d688ffc8..a7b8557bed 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -227,7 +227,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, timing off, summary off, costs off)
+ explain (analyze, regress)
select * from
(select ten from tenk1 where ten < 100 order by ten) ss
right join (values (1),(2),(3)) v(x) on true
@@ -394,7 +394,7 @@ explain (costs off)
-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 1;
-EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
+EXPLAIN (analyze, regress) SELECT * FROM tenk1;
ROLLBACK TO SAVEPOINT settings;
-- provoke error in worker
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index e879999708..5803be5617 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -802,7 +802,7 @@ $$
declare ln text;
begin
for ln in
- explain (analyze, summary off, timing off, costs off)
+ explain (analyze, regress)
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
loop
ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b6..7160327138 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, REGRESS)
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, REGRESS)
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, REGRESS)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ROLLBACK;
--
2.17.0
>From 190cee1d517fbdfac76d8a14671331fb33dd3811 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 22 Feb 2020 18:45:22 -0600
Subject: [PATCH 3/3] Add explain(MACHINE)...
..to allow regression tests with stable output, by using
explain(ANALYZE,MACHINE OFF).
This does *not* attempt to handle variations in "Workers Launched", or other
bits which would also need to be handled.
---
src/backend/commands/explain.c | 78 ++++++++++++-------
src/include/commands/explain.h | 1 +
.../regress/expected/incremental_sort.out | 4 +-
src/test/regress/expected/insert_conflict.out | 2 +-
src/test/regress/expected/select.out | 2 +-
src/test/regress/expected/select_parallel.out | 32 +++-----
src/test/regress/expected/subselect.out | 21 +----
src/test/regress/sql/insert_conflict.sql | 2 +-
src/test/regress/sql/select.sql | 2 +-
src/test/regress/sql/select_parallel.sql | 21 +----
src/test/regress/sql/subselect.sql | 19 +----
11 files changed, 78 insertions(+), 106 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index c5ade3554e..9a3f6cb0d5 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)
@@ -212,6 +213,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);
@@ -239,18 +245,15 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
parser_errposition(pstate, opt->location)));
}
- if (regress && (es->timing || es->summary ||
- (costs_set && es->costs))) // XXX
+ if (regress && (es->timing || es->summary || es->machine ||
+ (costs_set && es->costs)))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("EXPLAIN option REGRESS cannot be specified with any of: TIMING, SUMMARY, COSTS")));
+ errmsg("EXPLAIN option REGRESS cannot be specified with any of: TIMING, SUMMARY, COSTS, MACHINE")));
/* if the costs option was not set explicitly, set default value */
es->costs = (costs_set) ? es->costs : es->costs && !regress;
- /* if the buffers option was not set explicitly, set default value */
- es->buffers = (buffers_set) ? es->buffers : es->analyze && !regress;
-
if (es->wal && !es->analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -265,9 +268,21 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN option TIMING requires ANALYZE")));
+ /* check that MACHINE is used with EXPLAIN ANALYZE */
+ if (es->machine && !es->analyze)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN option MACHINE requires ANALYZE")));
+
/* if the summary was not set explicitly, set default value */
es->summary = (summary_set) ? es->summary : es->analyze && !regress;
+ /* if the machine option was not set explicitly, set default value */
+ es->machine = (machine_set) ? es->machine : es->analyze && !regress;
+
+ /* if the buffers option was not set explicitly, set default value */
+ es->buffers = (buffers_set) ? es->buffers : es->machine;
+
query = castNode(Query, stmt->query);
if (IsQueryIdEnabled())
jstate = JumbleQuery(query, pstate->p_sourcetext);
@@ -338,6 +353,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();
@@ -639,7 +655,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
}
/* Show buffer usage in planning */
- if (bufusage)
+ if (bufusage && es->buffers)
{
ExplainOpenGroup("Planning", "Planning", true, es);
show_buffer_usage(es, bufusage, true);
@@ -1779,7 +1795,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;
@@ -2753,8 +2769,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
{
@@ -2798,8 +2818,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
{
@@ -3087,25 +3111,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');
}
}
}
@@ -3379,6 +3404,7 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
static void
show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es)
{
+ // XXX: if (!es->machine) ; /* Do nothing */
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 e94d9e49cf..87361594fc 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/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 43de2745cf..0b64254297 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)
@@ -755,7 +755,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/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 66d8633e3e..e913f6f840 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -195,7 +195,7 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
(5 rows)
-- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array:
-explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *;
+explain (costs off, format json, regress) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *;
QUERY PLAN
------------------------------------------------------------------------
[ +
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index 2a94111282..dd42e90f2b 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -762,7 +762,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
Filter: (stringu1 = 'ATAAAA'::name)
(3 rows)
-explain (costs off)
+explain (costs off, regress)
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
QUERY PLAN
-----------------------------------------
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 36b853213e..f19fbf86fb 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -562,24 +562,11 @@ explain (analyze, regress)
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, regress)
- select * from
+explain (analyze, regress)
+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 ec75bd2132..a60524ce2a 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1526,27 +1526,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, regress)
- 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, regress)
+ 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)
@@ -1558,7 +1546,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/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 23d5778b82..02d102400b 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -83,7 +83,7 @@ explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on con
-- With EXCLUDED.* expression in scan node:
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry';
-- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array:
-explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *;
+explain (costs off, format json, regress) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *;
-- Fails (no unique index inference specification, required for do update variant):
insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit;
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index 1c757fe0bf..26433eff76 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -198,7 +198,7 @@ select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
-- actually run the query with an analyze to use the partial index
explain (analyze, regress)
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
-explain (costs off)
+explain (costs off, regress)
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
-- partial index predicate implies clause, so no need for retest
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index a7b8557bed..5f44100b8f 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -221,23 +221,11 @@ explain (analyze, regress)
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, regress)
- select * from
+
+explain (analyze, regress)
+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 +233,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 5803be5617..6e62f2b2b7 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -797,26 +797,11 @@ insert into sq_limit values
(7, 3, 3),
(8, 4, 4);
-create function explain_sq_limit() returns setof text language plpgsql as
-$$
-declare ln text;
-begin
- for ln in
- explain (analyze, regress)
- 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, regress)
+ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
-drop function explain_sq_limit();
-
drop table sq_limit;
--
--
2.17.0
