pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedBUFFERS enabled by default in EXPLAIN (ANALYZE)
8+ messages / 6 participants
[nested] [flat]
* BUFFERS enabled by default in EXPLAIN (ANALYZE)
@ 2021-11-12 22:58 Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-11-13 00:18 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Tomas Vondra <tomas.vondra@enterprisedb.com>
2021-11-13 01:34 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Vik Fearing <vik@postgresfriends.org>
2021-11-15 19:09 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 3 replies; 8+ messages in thread
From: Nikolay Samokhvalov @ 2021-11-12 22:58 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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.
Attached is a simple patch enabling BUFFERS by default, without involving
GUCs.
Why it is important?
In many cases, people forget about the BUFFERS option in EXPLAIN ANALYZE
and share execution plans without it – sending it via regular communication
channels for work or publishing to visualization systems. Meanwhile, the
option has a lower overhead compared to TIMING (enabled by default for
EXPLAIN ANALYZE) and it is extremely useful for query
optimization. This patch doesn't enable BUFFERS for EXPLAIN executed
without ANALYZE.
Open questions:
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.
2. How to adjust documentation? Should EXPLAIN ANALYZE examples be adjusted
to use BUFFERS OFF (easier change) or show some example buffer numbers –
like it is done for timing and cost numbers? I tend to think that the
latter makes more sense.
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).
On any given platform, the buffer numbers are pretty stable, so we could
rely on it, but I'm not sure about all possible options being tested and
would appreciate advice here (of course, if the patch makes it thru the
discussion in general).
## Links
[1]
https://www.postgresql.org/message-id/flat/b3197ba8-225f-f53c-326d-5b1756c77c3e%40postgresfriends.or...
[2] https://commitfest.postgresql.org/28/2567/
Attachments:
[application/octet-stream] 001-buffers-in-explain-analyze-enabled-by-default.patch (3.0K, ../../CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw@mail.gmail.com/3-001-buffers-in-explain-analyze-enabled-by-default.patch)
download | inline diff:
From d70b147d71ca15beb51187b75645587ed7de612f Mon Sep 17 00:00:00 2001
From: NikolayS <nik@postgres.ai>
Date: Fri, 12 Nov 2021 21:55:47 +0000
Subject: [PATCH] Enable BUFFERS by default in EXPLAIN ANALYZE
In many cases, people forget about the BUFFERS option in
EXPLAIN ANALYZE and share execution plans without it. Meanwhile,
the option has a lower overhead compared to TIMING (enabled by
default for EXPLAIN ANALYZE) and it is extremely useful for query
optimization.
This patch doesn't enable BUFFERS for EXPLAIN executed
without ANALYZE.
See also: https://www.postgresql.org/message-id/flat/b3197ba8-225f-f53c-326d-5b1756c77c3e%40postgresfriends.org
---
doc/src/sgml/ref/explain.sgml | 8 +++++---
src/backend/commands/explain.c | 10 ++++++++++
2 files changed, 15 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 4d758fb237..57a16ac32f 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -188,9 +188,11 @@ ROLLBACK;
previously-dirtied blocks evicted from cache by this backend during
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>.
+ upper-level node includes those used by all its child nodes. In text
+ format, only non-zero values are printed. It defaults to
+ <literal>TRUE</literal> for <literal>EXPLAIN ANALYZE</literal> and
+ to <literal>FALSE</literal> for <literal>EXPLAIN</literal> executed
+ without the <literal>ANALYZE</literal> option.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 10644dfac4..85491defc8 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -171,6 +171,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
List *rewritten;
ListCell *lc;
bool timing_set = false;
+ bool buffers_set = false;
bool summary_set = false;
/* Parse options list. */
@@ -185,7 +186,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
else if (strcmp(opt->defname, "costs") == 0)
es->costs = defGetBoolean(opt);
else if (strcmp(opt->defname, "buffers") == 0)
+ {
+ buffers_set = true;
es->buffers = defGetBoolean(opt);
+ }
else if (strcmp(opt->defname, "wal") == 0)
es->wal = defGetBoolean(opt);
else if (strcmp(opt->defname, "settings") == 0)
@@ -241,6 +245,12 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN option TIMING requires ANALYZE")));
+ /* if the buffers option was not set explicitly, set default value:
+ * - TRUE for EXPLAIN ANALYZE
+ * - FALSE for EXPLAIN without ANALYZE
+ */
+ es->buffers = (buffers_set) ? es->buffers : es->analyze;
+
/* if the summary was not set explicitly, set default value */
es->summary = (summary_set) ? es->summary : es->analyze;
--
GitLab
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
@ 2021-11-13 00:18 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2021-11-13 04:17 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Julien Rouhaud <rjuju123@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tomas Vondra @ 2021-11-13 00:18 UTC (permalink / raw)
To: Nikolay Samokhvalov <samokhvalov@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On 11/12/21 23:58, Nikolay Samokhvalov wrote:
> Re-reading the thread [1] (cannot answer there – don't have those emails
> in my box anymore),
You can download the message as mbox and import it into your client
(pretty much any client supports that, I think).
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-11-13 00:18 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Tomas Vondra <tomas.vondra@enterprisedb.com>
@ 2021-11-13 04:17 ` Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2021-11-13 04:17 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@enterprisedb.com>; +Cc: Nikolay Samokhvalov <samokhvalov@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Sat, Nov 13, 2021 at 8:18 AM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
>
> On 11/12/21 23:58, Nikolay Samokhvalov wrote:
> > Re-reading the thread [1] (cannot answer there – don't have those emails
> > in my box anymore),
>
> You can download the message as mbox and import it into your client
> (pretty much any client supports that, I think).
There is also a "resend mail" link available for each mail in the
archive which should be even more straightforward.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
@ 2021-11-13 01:34 ` Vik Fearing <vik@postgresfriends.org>
2 siblings, 0 replies; 8+ messages in thread
From: Vik Fearing @ 2021-11-13 01:34 UTC (permalink / raw)
To: Nikolay Samokhvalov <samokhvalov@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On 11/12/21 11:58 PM, 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.
>
> Attached is a simple patch enabling BUFFERS by default, without involving
> GUCs.
I obviously agree with this (although I still wish we had gucs as we
keep adding more and more options to EXPLAIN).
The patch looks good to me, too.
+1
--
Vik Fearing
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
@ 2021-11-15 19:09 ` Justin Pryzby <pryzby@telsasoft.com>
2021-11-24 18:35 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Michael Christofides <michael@pgmustard.com>
2021-12-02 00:58 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Justin Pryzby <pryzby@telsasoft.com>
2 siblings, 2 replies; 8+ messages in thread
From: Justin Pryzby @ 2021-11-15 19:09 UTC (permalink / raw)
To: Nikolay Samokhvalov <samokhvalov@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
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.GM684@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
Attachments:
[text/x-diff] 0001-Add-explain-REGRESS.patch (5.9K, ../../20211115190954.GO17618@telsasoft.com/2-0001-Add-explain-REGRESS.patch)
download | inline diff:
From cdf439d9ccdec0f26bdd6b86f9f27bd4e55e5b9e Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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
[text/x-diff] 0002-Make-explain-analyze-default-to-BUFFERS-TRUE.patch (49.2K, ../../20211115190954.GO17618@telsasoft.com/3-0002-Make-explain-analyze-default-to-BUFFERS-TRUE.patch)
download | inline diff:
From acd6c2065f7057f81ddf9efddfec7230e6b395dc Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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
[text/x-diff] 0003-Add-explain-MACHINE.patch (18.4K, ../../20211115190954.GO17618@telsasoft.com/4-0003-Add-explain-MACHINE.patch)
download | inline diff:
From 190cee1d517fbdfac76d8a14671331fb33dd3811 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-11-15 19:09 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Justin Pryzby <pryzby@telsasoft.com>
@ 2021-11-24 18:35 ` Michael Christofides <michael@pgmustard.com>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Christofides @ 2021-11-24 18:35 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Nikolay Samokhvalov <samokhvalov@gmail.com>; pgsql-hackers@lists.postgresql.org
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.
I’d be keen to see BUFFERS off by default with EXPLAIN, and on by default
with EXPLAIN ANALYZE.
The SUMMARY flag was implemented that way, which I think has been easy
enough for folks to understand and document. In fact, I think the only
BUFFERS information goes in the “summary” section for EXPLAIN (BUFFERS), so
maybe it makes perfect sense? If it would be great if that made
implementation easier, too.
In any case, thank you all, I’m so glad that this is being discussed again.
It’d be so good to start seeing buffers in more plans.
—
Michael
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-11-15 19:09 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Justin Pryzby <pryzby@telsasoft.com>
@ 2021-12-02 00:58 ` Justin Pryzby <pryzby@telsasoft.com>
2022-01-18 06:53 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Julien Rouhaud <rjuju123@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Justin Pryzby @ 2021-12-02 00:58 UTC (permalink / raw)
To: Nikolay Samokhvalov <samokhvalov@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Mon, Nov 15, 2021 at 01:09:54PM -0600, Justin Pryzby wrote:
> Some time ago, I had a few relevant patches:
> 1) add explain(REGRESS) which is shorthand for (BUFFERS OFF, TIMING OFF, COSTS OFF, SUMMARY OFF)
> 2) add explain(MACHINE) which elides machine-specific output from explain;
> for example, Heap Fetches, sort spaceUsed, hash nbuckets, and tidbitmap stuff.
>
> https://www.postgresql.org/message-id/flat/20200306213310.GM684@telsasoft.com
The attached patch series now looks like this (some minor patches are not
included in this list):
1. add GUC explain_regress, which disables TIMING, SUMMARY, COSTS;
2. enable explain(BUFFERS) by default (but disabled by explain_regress);
3. Add explain(MACHINE) - which is disabled by explain_regress.
This elides various machine-specific output like Memory and Disk use.
Maybe it should be called something else like "QUIET" or "VERBOSE_MINUS_1"
or ??
The regression tests now automatically run with explain_regress=on, which is
shorthand for TIMING OFF, SUMMARY OFF, COSTS OFF, and then BUFFERS OFF.
There's a further option called explain(MACHINE) which can be disabled to hide
portions of the output that are unstable, like Memory/Disk/Batches/
Heap Fetches/Heap Blocks. This allows "explain analyze" to be used more easily
in regression tests, and simplifies some existing tests that currently use
plpgsql functions to filter the output. But it doesn't handle all the
variations from parallel workers.
(3) is optional, but simplifies some regression tests. The patch series could
be rephrased with (3) first.
Unfortunately, "COSTS OFF" breaks postgres_fdw remote_estimate. If specifying
"COSTS ON" in postgres_fdw.c is considered to be a poor fix , then I suppose
this patch series could do as suggested and enable buffers by default only when
ANALYZE is specified. Then postgres_fdw is not affected, and the
explain_regress GUC is optional: instead, we'd need to specify BUFFERS OFF in
~100 regression tests which use EXPLAIN ANALYZE. (3) still seems useful on its
own.
Attachments:
[text/x-diff] 0001-Add-GUC-explain_regress.patch (9.3K, ../../20211202005820.GJ17618@telsasoft.com/2-0001-Add-GUC-explain_regress.patch)
download | inline diff:
From 6804bc6945f7c7e0d51beb1930b80cf2ee0e3f82 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 22 Feb 2020 21:17:10 -0600
Subject: [PATCH 01/11] Add GUC: explain_regress
This changes the defaults for explain to: costs off, timing off, summary off.
It'd be reasonable to use this for new regression tests which are not intended
to be backpatched.
---
contrib/postgres_fdw/postgres_fdw.c | 2 +-
src/backend/commands/explain.c | 13 +++++++++++--
src/backend/utils/misc/guc.c | 13 +++++++++++++
src/include/commands/explain.h | 2 ++
src/test/regress/expected/explain.out | 3 +++
src/test/regress/expected/inherit.out | 2 +-
src/test/regress/expected/stats_ext.out | 2 +-
src/test/regress/pg_regress.c | 3 ++-
src/test/regress/sql/explain.sql | 4 ++++
src/test/regress/sql/inherit.sql | 2 +-
src/test/regress/sql/stats_ext.sql | 2 +-
11 files changed, 40 insertions(+), 8 deletions(-)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index fa9a099f13..5cd90d7404 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3112,7 +3112,7 @@ estimate_path_cost_size(PlannerInfo *root,
* values, so don't request params_list.
*/
initStringInfo(&sql);
- appendStringInfoString(&sql, "EXPLAIN ");
+ appendStringInfoString(&sql, "EXPLAIN (COSTS)");
deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist,
remote_conds, pathkeys,
fpextra ? fpextra->has_final_sort : false,
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 09f5253abb..e5362fb560 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -154,6 +154,7 @@ static void ExplainJSONLineEnding(ExplainState *es);
static void ExplainYAMLLineStarting(ExplainState *es);
static void escape_yaml(StringInfo buf, const char *str);
+bool explain_regress = false; /* GUC */
/*
@@ -172,6 +173,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
ListCell *lc;
bool timing_set = false;
bool summary_set = false;
+ bool costs_set = false;
/* Parse options list. */
foreach(lc, stmt->options)
@@ -183,7 +185,11 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
else if (strcmp(opt->defname, "verbose") == 0)
es->verbose = defGetBoolean(opt);
else if (strcmp(opt->defname, "costs") == 0)
+ {
+ /* Need to keep track if it was explicitly set to ON */
+ costs_set = true;
es->costs = defGetBoolean(opt);
+ }
else if (strcmp(opt->defname, "buffers") == 0)
es->buffers = defGetBoolean(opt);
else if (strcmp(opt->defname, "wal") == 0)
@@ -227,13 +233,16 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
parser_errposition(pstate, opt->location)));
}
+ /* if the costs option was not set explicitly, set default value */
+ es->costs = (costs_set) ? es->costs : es->costs && !explain_regress;
+
if (es->wal && !es->analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN option WAL requires ANALYZE")));
/* if the timing was not set explicitly, set default value */
- es->timing = (timing_set) ? es->timing : es->analyze;
+ es->timing = (timing_set) ? es->timing : es->analyze && !explain_regress;
/* check that timing is used with EXPLAIN ANALYZE */
if (es->timing && !es->analyze)
@@ -242,7 +251,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
errmsg("EXPLAIN option TIMING requires ANALYZE")));
/* if the summary was not set explicitly, set default value */
- es->summary = (summary_set) ? es->summary : es->analyze;
+ es->summary = (summary_set) ? es->summary : es->analyze && !explain_regress;
query = castNode(Query, stmt->query);
if (IsQueryIdEnabled())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index a292498e2f..9f791a01bb 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -45,6 +45,7 @@
#include "catalog/pg_authid.h"
#include "catalog/storage.h"
#include "commands/async.h"
+#include "commands/explain.h"
#include "commands/prepare.h"
#include "commands/trigger.h"
#include "commands/user.h"
@@ -1471,6 +1472,18 @@ static struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+
+ {
+ {"explain_regress", PGC_USERSET, DEVELOPER_OPTIONS,
+ gettext_noop("Change defaults of EXPLAIN to avoid unstable output."),
+ NULL,
+ GUC_NOT_IN_SAMPLE | GUC_EXPLAIN
+ },
+ &explain_regress,
+ false,
+ NULL, NULL, NULL
+ },
+
{
{"log_parser_stats", PGC_SUSET, STATS_MONITORING,
gettext_noop("Writes parser performance statistics to the server log."),
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index e94d9e49cf..d849bae451 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -61,6 +61,8 @@ typedef struct ExplainState
ExplainWorkersState *workers_state; /* needed if parallel plan */
} ExplainState;
+extern bool explain_regress;
+
/* Hook for plugins to get control in ExplainOneQuery() */
typedef void (*ExplainOneQuery_hook_type) (Query *query,
int cursorOptions,
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 1734dfee8c..188dc7ccec 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -8,6 +8,9 @@
-- To produce stable regression test output, it's usually necessary to
-- ignore details such as exact costs or row counts. These filter
-- functions replace changeable output details with fixed strings.
+-- Output normal, user-facing details, not the sanitized version used for the
+-- rest of the regression tests
+set explain_regress = off;
create function explain_filter(text) returns setof text
language plpgsql as
$$
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 2d49e765de..38fb5f94c6 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -664,7 +664,7 @@ select tableoid::regclass::text as relname, parted_tab.* from parted_tab order b
(3 rows)
-- modifies partition key, but no rows will actually be updated
-explain update parted_tab set a = 2 where false;
+explain (costs on) update parted_tab set a = 2 where false;
QUERY PLAN
--------------------------------------------------------
Update on parted_tab (cost=0.00..0.00 rows=0 width=0)
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index c60ba45aba..f7f516ea1c 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -14,7 +14,7 @@ declare
first_row bool := true;
begin
for ln in
- execute format('explain analyze %s', $1)
+ execute format('explain (analyze, costs on) %s', $1)
loop
if first_row then
first_row := false;
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index 2c8a600bad..255531580e 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -801,7 +801,7 @@ initialize_environment(void)
* user's ability to set other variables through that.
*/
{
- const char *my_pgoptions = "-c intervalstyle=postgres_verbose";
+ const char *my_pgoptions = "-c intervalstyle=postgres_verbose -c explain_regress=on";
const char *old_pgoptions = getenv("PGOPTIONS");
char *new_pgoptions;
@@ -2469,6 +2469,7 @@ regression_main(int argc, char *argv[],
fputs("log_lock_waits = on\n", pg_conf);
fputs("log_temp_files = 128kB\n", pg_conf);
fputs("max_prepared_transactions = 2\n", pg_conf);
+ // fputs("explain_regress = yes\n", pg_conf);
for (sl = temp_configs; sl != NULL; sl = sl->next)
{
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 84549c78fa..f91d1004d5 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -10,6 +10,10 @@
-- ignore details such as exact costs or row counts. These filter
-- functions replace changeable output details with fixed strings.
+-- Output normal, user-facing details, not the sanitized version used for the
+-- rest of the regression tests
+set explain_regress = off;
+
create function explain_filter(text) returns setof text
language plpgsql as
$$
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 195aedb5ff..868ee58b80 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -169,7 +169,7 @@ where parted_tab.a = ss.a;
select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2;
-- modifies partition key, but no rows will actually be updated
-explain update parted_tab set a = 2 where false;
+explain (costs on) update parted_tab set a = 2 where false;
drop table parted_tab;
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 6fb37962a7..c544e54a35 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -16,7 +16,7 @@ declare
first_row bool := true;
begin
for ln in
- execute format('explain analyze %s', $1)
+ execute format('explain (analyze, costs on) %s', $1)
loop
if first_row then
first_row := false;
--
2.17.0
[text/x-diff] 0002-exercise-explain_regress.patch (12.8K, ../../20211202005820.GJ17618@telsasoft.com/3-0002-exercise-explain_regress.patch)
download | inline diff:
From f7758a9b51f424c089354a4004795d9187d87a45 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Mon, 15 Nov 2021 21:54:12 -0600
Subject: [PATCH 02/11] exercise explain_regress
not intended to be merged, since it creates backpatch hazards (unless the GUC
is also backpatched)
---
src/test/regress/expected/matview.out | 12 ++++++------
src/test/regress/expected/select_into.out | 20 ++++++++++----------
src/test/regress/expected/tidscan.out | 6 +++---
src/test/regress/sql/matview.sql | 12 ++++++------
src/test/regress/sql/select_into.sql | 20 ++++++++++----------
src/test/regress/sql/tidscan.sql | 6 +++---
6 files changed, 38 insertions(+), 38 deletions(-)
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 313c72a268..e7ce5bb188 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -606,7 +606,7 @@ GRANT ALL ON SCHEMA matview_schema TO public;
SET SESSION AUTHORIZATION regress_matview_user;
CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
SELECT generate_series(1, 10) WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
SELECT generate_series(1, 10) WITH DATA;
QUERY PLAN
@@ -618,7 +618,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
SELECT generate_series(1, 10) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
SELECT generate_series(1, 10) WITH NO DATA;
QUERY PLAN
@@ -651,11 +651,11 @@ ERROR: relation "matview_ine_tab" already exists
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE: relation "matview_ine_tab" already exists, skipping
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_ine_tab AS
SELECT 1 / 0; -- error
ERROR: relation "matview_ine_tab" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0; -- ok
NOTICE: relation "matview_ine_tab" already exists, skipping
@@ -663,11 +663,11 @@ NOTICE: relation "matview_ine_tab" already exists, skipping
------------
(0 rows)
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- error
ERROR: relation "matview_ine_tab" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE: relation "matview_ine_tab" already exists, skipping
diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/expected/select_into.out
index 43b8209d22..3de0be2a15 100644
--- a/src/test/regress/expected/select_into.out
+++ b/src/test/regress/expected/select_into.out
@@ -25,7 +25,7 @@ CREATE TABLE selinto_schema.tbl_withdata1 (a)
AS SELECT generate_series(1,3) WITH DATA;
INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
ERROR: permission denied for table tbl_withdata1
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
SELECT generate_series(1,3) WITH DATA;
QUERY PLAN
@@ -37,7 +37,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
-- WITH NO DATA, passes.
CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
SELECT generate_series(1,3) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
SELECT generate_series(1,3) WITH NO DATA;
QUERY PLAN
@@ -50,7 +50,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
PREPARE data_sel AS SELECT generate_series(1,3);
CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
EXECUTE data_sel WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
EXECUTE data_sel WITH DATA;
QUERY PLAN
@@ -62,7 +62,7 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
-- EXECUTE and WITH NO DATA, passes.
CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
EXECUTE data_sel WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
EXECUTE data_sel WITH NO DATA;
QUERY PLAN
@@ -188,20 +188,20 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
ERROR: relation "ctas_ine_tbl" already exists
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
ERROR: relation "ctas_ine_tbl" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
QUERY PLAN
------------
(0 rows)
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
ERROR: relation "ctas_ine_tbl" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
QUERY PLAN
@@ -209,10 +209,10 @@ NOTICE: relation "ctas_ine_tbl" already exists, skipping
(0 rows)
PREPARE ctas_ine_query AS SELECT 1 / 0;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
ERROR: relation "ctas_ine_tbl" already exists
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
QUERY PLAN
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index 13c3c360c2..de93145bf0 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -189,7 +189,7 @@ FETCH NEXT FROM c;
(1 row)
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
QUERY PLAN
---------------------------------------------------
@@ -205,7 +205,7 @@ FETCH NEXT FROM c;
(1 row)
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
QUERY PLAN
---------------------------------------------------
@@ -229,7 +229,7 @@ FETCH NEXT FROM c;
(0 rows)
-- should error out
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ERROR: cursor "c" is not positioned on a row
ROLLBACK;
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index 68b9ccfd45..e0b562933d 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -255,13 +255,13 @@ GRANT ALL ON SCHEMA matview_schema TO public;
SET SESSION AUTHORIZATION regress_matview_user;
CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
SELECT generate_series(1, 10) WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
SELECT generate_series(1, 10) WITH DATA;
REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
SELECT generate_series(1, 10) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
SELECT generate_series(1, 10) WITH NO DATA;
REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
@@ -282,16 +282,16 @@ CREATE MATERIALIZED VIEW matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- error
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_ine_tab AS
SELECT 1 / 0; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
SELECT 1 / 0 WITH NO DATA; -- ok
DROP MATERIALIZED VIEW matview_ine_tab;
diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/select_into.sql
index 7e903c339a..7031e86a2c 100644
--- a/src/test/regress/sql/select_into.sql
+++ b/src/test/regress/sql/select_into.sql
@@ -30,26 +30,26 @@ SET SESSION AUTHORIZATION regress_selinto_user;
CREATE TABLE selinto_schema.tbl_withdata1 (a)
AS SELECT generate_series(1,3) WITH DATA;
INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
SELECT generate_series(1,3) WITH DATA;
-- WITH NO DATA, passes.
CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
SELECT generate_series(1,3) WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
SELECT generate_series(1,3) WITH NO DATA;
-- EXECUTE and WITH DATA, passes.
PREPARE data_sel AS SELECT generate_series(1,3);
CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
EXECUTE data_sel WITH DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
EXECUTE data_sel WITH DATA;
-- EXECUTE and WITH NO DATA, passes.
CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
EXECUTE data_sel WITH NO DATA;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
EXECUTE data_sel WITH NO DATA;
RESET SESSION AUTHORIZATION;
@@ -122,17 +122,17 @@ CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
PREPARE ctas_ine_query AS SELECT 1 / 0;
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
DROP TABLE ctas_ine_tbl;
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b6..3d1f447088 100644
--- a/src/test/regress/sql/tidscan.sql
+++ b/src/test/regress/sql/tidscan.sql
@@ -68,17 +68,17 @@ DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan;
FETCH NEXT FROM c; -- skip one row
FETCH NEXT FROM c;
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
FETCH NEXT FROM c;
-- perform update
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
SELECT * FROM tidscan;
-- position cursor past any rows
FETCH NEXT FROM c;
-- should error out
-EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+EXPLAIN (ANALYZE)
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
ROLLBACK;
--
2.17.0
[text/x-diff] 0003-Make-explain-default-to-BUFFERS-TRUE.patch (4.6K, ../../20211202005820.GJ17618@telsasoft.com/4-0003-Make-explain-default-to-BUFFERS-TRUE.patch)
download | inline diff:
From d851c4348dfe88147d977977a5a19e592f02e94b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 22 Jul 2020 19:20:40 -0500
Subject: [PATCH 03/11] Make explain default to BUFFERS TRUE
---
contrib/auto_explain/auto_explain.c | 4 ++--
doc/src/sgml/config.sgml | 4 ++--
doc/src/sgml/perform.sgml | 1 +
doc/src/sgml/ref/explain.sgml | 2 +-
src/backend/commands/explain.c | 8 ++++++++
5 files changed, 14 insertions(+), 5 deletions(-)
diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 59ba63455f..91f59124b8 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -27,7 +27,7 @@ PG_MODULE_MAGIC;
static int auto_explain_log_min_duration = -1; /* msec or -1 */
static bool auto_explain_log_analyze = false;
static bool auto_explain_log_verbose = false;
-static bool auto_explain_log_buffers = false;
+static bool auto_explain_log_buffers = true;
static bool auto_explain_log_wal = false;
static bool auto_explain_log_triggers = false;
static bool auto_explain_log_timing = true;
@@ -143,7 +143,7 @@ _PG_init(void)
"Log buffers usage.",
NULL,
&auto_explain_log_buffers,
- false,
+ true,
PGC_SUSET,
0,
NULL,
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ab617c7b86..a3f1511e12 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7615,8 +7615,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
displayed in <link linkend="monitoring-pg-stat-database-view">
<structname>pg_stat_database</structname></link>, in the output of
<xref linkend="sql-explain"/> when the <literal>BUFFERS</literal> option
- is used, by autovacuum for auto-vacuums and auto-analyzes, when
- <xref linkend="guc-log-autovacuum-min-duration"/> is set and by
+ is enabled, by autovacuum for auto-vacuums and auto-analyzes, when
+ <xref linkend="guc-log-autovacuum-min-duration"/> is set, and by
<xref linkend="pgstatstatements"/>. Only superusers can change this
setting.
</para>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e..16383b8f5f 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -731,6 +731,7 @@ EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
</para>
<para>
+XXX
<command>EXPLAIN</command> has a <literal>BUFFERS</literal> option that can be used with
<literal>ANALYZE</literal> to get even more run time statistics:
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 4d758fb237..ceb0f4c83a 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -190,7 +190,7 @@ ROLLBACK;
The number of blocks shown for an
upper-level node includes those used by all its child nodes. In text
format, only non-zero values are printed. It defaults to
- <literal>FALSE</literal>.
+ <literal>TRUE</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index e5362fb560..050b7467c6 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -174,6 +174,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
bool timing_set = false;
bool summary_set = false;
bool costs_set = false;
+ bool buffers_set = false;
/* Parse options list. */
foreach(lc, stmt->options)
@@ -191,7 +192,10 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
es->costs = defGetBoolean(opt);
}
else if (strcmp(opt->defname, "buffers") == 0)
+ {
+ buffers_set = true;
es->buffers = defGetBoolean(opt);
+ }
else if (strcmp(opt->defname, "wal") == 0)
es->wal = defGetBoolean(opt);
else if (strcmp(opt->defname, "settings") == 0)
@@ -253,6 +257,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
/* if the summary was not set explicitly, set default value */
es->summary = (summary_set) ? es->summary : es->analyze && !explain_regress;
+ /* if the buffers option was not set explicitly, set default value */
+ es->buffers = (buffers_set) ? es->buffers : !explain_regress;
+
query = castNode(Query, stmt->query);
if (IsQueryIdEnabled())
jstate = JumbleQuery(query, pstate->p_sourcetext);
@@ -323,6 +330,7 @@ NewExplainState(void)
/* Set default options (most fields can be left as zeroes). */
es->costs = true;
+ es->buffers = true;
/* Prepare output buffer. */
es->str = makeStringInfo();
--
2.17.0
[text/x-diff] 0004-Add-explain-MACHINE-to-hide-machine-dependent-output.patch (33.8K, ../../20211202005820.GJ17618@telsasoft.com/5-0004-Add-explain-MACHINE-to-hide-machine-dependent-output.patch)
download | inline diff:
From 2573d706e8985221d2795194bad3060720afa765 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 22 Feb 2020 18:45:22 -0600
Subject: [PATCH 04/11] Add explain(MACHINE) to hide machine-dependent output..
This new option hides some output that has traditionally been shown; the option
is enabled by regression mode to hide unstable output.
This allows EXPLAIN ANALYZE to be used in regression tests with stable output.
This is like a "quiet" mode, or negative verbosity.
Also add regression tests for HashAgg and Bitmap scan, which previously had no
tests with explain(analyze).
This does not handle variations in "Workers Launched", or other parallel worker
bits which are handled by force_parallel_mode=regress.
Also add tests for show_hashagg_info and tidbitmap, for which there's no other
test.
---
src/backend/commands/explain.c | 77 +++++++++++++------
src/include/commands/explain.h | 1 +
src/test/isolation/expected/horizons.out | 40 +++++-----
src/test/isolation/specs/horizons.spec | 2 +-
src/test/regress/expected/explain.out | 55 +++++++++++++
.../regress/expected/incremental_sort.out | 4 +-
src/test/regress/expected/memoize.out | 35 ++++-----
src/test/regress/expected/partition_prune.out | 4 +-
src/test/regress/expected/select_parallel.out | 32 +++-----
src/test/regress/expected/subselect.out | 21 +----
src/test/regress/sql/explain.sql | 17 ++++
src/test/regress/sql/memoize.sql | 4 +-
src/test/regress/sql/select_parallel.sql | 20 +----
src/test/regress/sql/subselect.sql | 19 +----
14 files changed, 182 insertions(+), 149 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 050b7467c6..833daac940 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -175,6 +175,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
bool summary_set = false;
bool costs_set = false;
bool buffers_set = false;
+ bool machine_set = false;
/* Parse options list. */
foreach(lc, stmt->options)
@@ -210,6 +211,11 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
summary_set = true;
es->summary = defGetBoolean(opt);
}
+ else if (strcmp(opt->defname, "machine") == 0)
+ {
+ machine_set = true;
+ es->machine = defGetBoolean(opt);
+ }
else if (strcmp(opt->defname, "format") == 0)
{
char *p = defGetString(opt);
@@ -260,6 +266,9 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
/* if the buffers option was not set explicitly, set default value */
es->buffers = (buffers_set) ? es->buffers : !explain_regress;
+ /* if the machine option was not set explicitly, set default value */
+ es->machine = (machine_set) ? es->machine : !explain_regress;
+
query = castNode(Query, stmt->query);
if (IsQueryIdEnabled())
jstate = JumbleQuery(query, pstate->p_sourcetext);
@@ -621,7 +630,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
/* Create textual dump of plan tree */
ExplainPrintPlan(es, queryDesc);
- if (es->verbose && plannedstmt->queryId != UINT64CONST(0))
+ if (es->verbose && plannedstmt->queryId != UINT64CONST(0) && es->machine)
{
/*
* Output the queryid as an int64 rather than a uint64 so we match
@@ -632,7 +641,7 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
}
/* Show buffer usage in planning */
- if (bufusage)
+ if (bufusage && es->buffers)
{
ExplainOpenGroup("Planning", "Planning", true, es);
show_buffer_usage(es, bufusage, true);
@@ -1772,7 +1781,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
if (plan->qual)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
- if (es->analyze)
+ if (es->analyze && es->machine)
ExplainPropertyFloat("Heap Fetches", NULL,
planstate->instrument->ntuples2, 0, es);
break;
@@ -2746,8 +2755,12 @@ show_sort_info(SortState *sortstate, ExplainState *es)
if (es->format == EXPLAIN_FORMAT_TEXT)
{
ExplainIndentText(es);
- appendStringInfo(es->str, "Sort Method: %s %s: " INT64_FORMAT "kB\n",
- sortMethod, spaceType, spaceUsed);
+ appendStringInfo(es->str, "Sort Method: %s",
+ sortMethod);
+ if (es->machine)
+ appendStringInfo(es->str, " %s: " INT64_FORMAT "kB",
+ spaceType, spaceUsed);
+ appendStringInfoString(es->str, "\n");
}
else
{
@@ -2791,8 +2804,12 @@ show_sort_info(SortState *sortstate, ExplainState *es)
{
ExplainIndentText(es);
appendStringInfo(es->str,
- "Sort Method: %s %s: " INT64_FORMAT "kB\n",
- sortMethod, spaceType, spaceUsed);
+ "Sort Method: %s",
+ sortMethod);
+ if (es->machine)
+ appendStringInfo(es->str, " %s: " INT64_FORMAT "kB", spaceType, spaceUsed);
+
+ appendStringInfoString(es->str, "\n");
}
else
{
@@ -3080,25 +3097,26 @@ show_hash_info(HashState *hashstate, ExplainState *es)
ExplainPropertyInteger("Peak Memory Usage", "kB",
spacePeakKb, es);
}
- else if (hinstrument.nbatch_original != hinstrument.nbatch ||
- hinstrument.nbuckets_original != hinstrument.nbuckets)
+ else
{
ExplainIndentText(es);
- appendStringInfo(es->str,
- "Buckets: %d (originally %d) Batches: %d (originally %d) Memory Usage: %ldkB\n",
+ if (hinstrument.nbatch_original != hinstrument.nbatch ||
+ hinstrument.nbuckets_original != hinstrument.nbuckets)
+ appendStringInfo(es->str,
+ "Buckets: %d (originally %d) Batches: %d (originally %d)",
hinstrument.nbuckets,
hinstrument.nbuckets_original,
hinstrument.nbatch,
- hinstrument.nbatch_original,
- spacePeakKb);
- }
- else
- {
- ExplainIndentText(es);
- appendStringInfo(es->str,
- "Buckets: %d Batches: %d Memory Usage: %ldkB\n",
- hinstrument.nbuckets, hinstrument.nbatch,
- spacePeakKb);
+ hinstrument.nbatch_original);
+ else
+ appendStringInfo(es->str,
+ "Buckets: %d Batches: %d",
+ hinstrument.nbuckets, hinstrument.nbatch);
+
+ if (es->machine)
+ appendStringInfo(es->str, " Memory Usage: %ldkB", spacePeakKb);
+
+ appendStringInfoChar(es->str, '\n');
}
}
}
@@ -3182,12 +3200,16 @@ show_memoize_info(MemoizeState *mstate, List *ancestors, ExplainState *es)
{
ExplainIndentText(es);
appendStringInfo(es->str,
- "Hits: " UINT64_FORMAT " Misses: " UINT64_FORMAT " Evictions: " UINT64_FORMAT " Overflows: " UINT64_FORMAT " Memory Usage: " INT64_FORMAT "kB\n",
+ "Hits: " UINT64_FORMAT " Misses: " UINT64_FORMAT " Evictions: " UINT64_FORMAT " Overflows: " UINT64_FORMAT,
mstate->stats.cache_hits,
mstate->stats.cache_misses,
mstate->stats.cache_evictions,
- mstate->stats.cache_overflows,
+ mstate->stats.cache_overflows);
+ if (es->machine)
+ appendStringInfo(es->str, " Memory Usage: " INT64_FORMAT "kB",
memPeakKb);
+
+ appendStringInfoChar(es->str, '\n');
}
}
@@ -3256,13 +3278,16 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
Agg *agg = (Agg *) aggstate->ss.ps.plan;
int64 memPeakKb = (aggstate->hash_mem_peak + 1023) / 1024;
+ /* XXX: there's nothing portable we can show here ? */
+ if (!es->machine)
+ return;
+
if (agg->aggstrategy != AGG_HASHED &&
agg->aggstrategy != AGG_MIXED)
return;
if (es->format != EXPLAIN_FORMAT_TEXT)
{
-
if (es->costs)
ExplainPropertyInteger("Planned Partitions", NULL,
aggstate->hash_planned_partitions, es);
@@ -3375,6 +3400,10 @@ show_hashagg_info(AggState *aggstate, ExplainState *es)
static void
show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es)
{
+ /* XXX: there's nothing portable we can show here ? */
+ if (!es->machine)
+ return;
+
if (es->format != EXPLAIN_FORMAT_TEXT)
{
ExplainPropertyInteger("Exact Heap Blocks", NULL,
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index d849bae451..56817e31ef 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -46,6 +46,7 @@ typedef struct ExplainState
bool timing; /* print detailed node timing */
bool summary; /* print total planning and execution timing */
bool settings; /* print modified settings */
+ bool machine; /* print memory/disk and other machine-specific output */
ExplainFormat format; /* output format */
/* state for output formatting --- not reset for each new plan tree */
int indent; /* current indentation level */
diff --git a/src/test/isolation/expected/horizons.out b/src/test/isolation/expected/horizons.out
index 4150b2dee6..ee3e495a64 100644
--- a/src/test/isolation/expected/horizons.out
+++ b/src/test/isolation/expected/horizons.out
@@ -24,7 +24,7 @@ Index Only Scan using horizons_tst_data_key on horizons_tst
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -34,7 +34,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -47,7 +47,7 @@ step pruner_delete:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -57,7 +57,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -94,7 +94,7 @@ Index Only Scan using horizons_tst_data_key on horizons_tst
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -104,7 +104,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -117,7 +117,7 @@ step pruner_delete:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -127,7 +127,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -156,7 +156,7 @@ step ll_start:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -166,7 +166,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -180,7 +180,7 @@ step pruner_delete:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -190,7 +190,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -220,7 +220,7 @@ step ll_start:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -230,7 +230,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -246,7 +246,7 @@ step pruner_vacuum:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -256,7 +256,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -285,7 +285,7 @@ step ll_start:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -295,7 +295,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -311,7 +311,7 @@ step pruner_vacuum:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
@@ -321,7 +321,7 @@ step pruner_query:
step pruner_query:
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
?column?
diff --git a/src/test/isolation/specs/horizons.spec b/src/test/isolation/specs/horizons.spec
index d5239ff228..082205d36b 100644
--- a/src/test/isolation/specs/horizons.spec
+++ b/src/test/isolation/specs/horizons.spec
@@ -82,7 +82,7 @@ step pruner_vacuum
step pruner_query
{
SELECT explain_json($$
- EXPLAIN (FORMAT json, BUFFERS, ANALYZE)
+ EXPLAIN (FORMAT json, BUFFERS, ANALYZE, machine)
SELECT * FROM horizons_tst ORDER BY data;$$)->0->'Plan'->'Heap Fetches';
}
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 188dc7ccec..36c20e0680 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -276,6 +276,61 @@ select explain_filter('explain (buffers, format json) select * from int8_tbl i8'
]
(1 row)
+-- HashAgg
+begin;
+SET work_mem='64kB';
+select explain_filter('explain (analyze) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------
+ HashAggregate (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Group Key: a
+ Batches: N Memory Usage: NkB Disk Usage: NkB
+ -> Function Scan on generate_series a (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+select explain_filter('explain (analyze, machine off) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------
+ HashAggregate (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Group Key: a
+ -> Function Scan on generate_series a (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(5 rows)
+
+rollback;
+-- Bitmap scan
+begin;
+SET enable_indexscan=no;
+CREATE TABLE explainbitmap AS SELECT i AS a FROM generate_series(1,999) AS i;
+ANALYZE explainbitmap;
+CREATE INDEX ON explainbitmap(a);
+select explain_filter('explain (analyze) SELECT * FROM explainbitmap WHERE a<9');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on explainbitmap (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Recheck Cond: (a < N)
+ Heap Blocks: exact=N
+ -> Bitmap Index Scan on explainbitmap_a_idx (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Index Cond: (a < N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(7 rows)
+
+select explain_filter('explain (analyze, machine off) SELECT * FROM explainbitmap WHERE a<9');
+ explain_filter
+----------------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on explainbitmap (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Recheck Cond: (a < N)
+ -> Bitmap Index Scan on explainbitmap_a_idx (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Index Cond: (a < N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+rollback;
-- SETTINGS option
-- We have to ignore other settings that might be imposed by the environment,
-- so printing the whole Settings field unfortunately won't do.
diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out
index 545e301e48..56a32df536 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -542,7 +542,7 @@ select explain_analyze_without_memory('select * from (select * from t order by a
Full-sort Groups: 2 Sort Methods: top-N heapsort, quicksort Average Memory: NNkB Peak Memory: NNkB
-> Sort (actual rows=101 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: NNkB
+ Sort Method: quicksort
-> Seq Scan on t (actual rows=1000 loops=1)
(9 rows)
@@ -745,7 +745,7 @@ select explain_analyze_without_memory('select * from (select * from t order by a
Pre-sorted Groups: 5 Sort Methods: top-N heapsort, quicksort Average Memory: NNkB Peak Memory: NNkB
-> Sort (actual rows=1000 loops=1)
Sort Key: t.a
- Sort Method: quicksort Memory: NNkB
+ Sort Method: quicksort
-> Seq Scan on t (actual rows=1000 loops=1)
(10 rows)
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 4ca0bd1f1e..fbe6bed433 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -21,9 +21,7 @@ begin
end if;
ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
- ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
- ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
- ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
+ ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
return next ln;
end loop;
end;
@@ -45,11 +43,10 @@ WHERE t2.unique1 < 1000;', false);
-> Memoize (actual rows=1 loops=N)
Cache Key: t2.twenty
Cache Mode: logical
- Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 980 Misses: 20 Evictions: Zero Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.twenty)
- Heap Fetches: N
-(12 rows)
+(11 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -75,11 +72,10 @@ WHERE t1.unique1 < 1000;', false);
-> Memoize (actual rows=1 loops=N)
Cache Key: t1.twenty
Cache Mode: logical
- Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 980 Misses: 20 Evictions: Zero Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
Index Cond: (unique1 = t1.twenty)
- Heap Fetches: N
-(12 rows)
+(11 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -110,11 +106,10 @@ WHERE t2.unique1 < 1200;', true);
-> Memoize (actual rows=1 loops=N)
Cache Key: t2.thousand
Cache Mode: logical
- Hits: N Misses: N Evictions: N Overflows: 0 Memory Usage: NkB
+ Hits: N Misses: N Evictions: N Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.thousand)
- Heap Fetches: N
-(12 rows)
+(11 rows)
CREATE TABLE flt (f float);
CREATE INDEX flt_f_idx ON flt (f);
@@ -128,15 +123,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f = f2.f;', false);
-------------------------------------------------------------------------------
Nested Loop (actual rows=4 loops=N)
-> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
- Heap Fetches: N
-> Memoize (actual rows=2 loops=N)
Cache Key: f1.f
Cache Mode: logical
- Hits: 1 Misses: 1 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 1 Misses: 1 Evictions: Zero Overflows: 0
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f = f1.f)
- Heap Fetches: N
-(10 rows)
+(8 rows)
-- Ensure memoize operates in binary mode
SELECT explain_memoize('
@@ -145,15 +138,13 @@ SELECT * FROM flt f1 INNER JOIN flt f2 ON f1.f >= f2.f;', false);
-------------------------------------------------------------------------------
Nested Loop (actual rows=4 loops=N)
-> Index Only Scan using flt_f_idx on flt f1 (actual rows=2 loops=N)
- Heap Fetches: N
-> Memoize (actual rows=2 loops=N)
Cache Key: f1.f
Cache Mode: binary
- Hits: 0 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 0 Misses: 2 Evictions: Zero Overflows: 0
-> Index Only Scan using flt_f_idx on flt f2 (actual rows=2 loops=N)
Index Cond: (f <= f1.f)
- Heap Fetches: N
-(10 rows)
+(8 rows)
DROP TABLE flt;
-- Exercise Memoize in binary mode with a large fixed width type and a
@@ -175,7 +166,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.n >= s2.n;', false);
-> Memoize (actual rows=4 loops=N)
Cache Key: s1.n
Cache Mode: binary
- Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 3 Misses: 3 Evictions: Zero Overflows: 0
-> Index Scan using strtest_n_idx on strtest s2 (actual rows=4 loops=N)
Index Cond: (n <= s1.n)
(8 rows)
@@ -190,7 +181,7 @@ SELECT * FROM strtest s1 INNER JOIN strtest s2 ON s1.t >= s2.t;', false);
-> Memoize (actual rows=4 loops=N)
Cache Key: s1.t
Cache Mode: binary
- Hits: 3 Misses: 3 Evictions: Zero Overflows: 0 Memory Usage: NkB
+ Hits: 3 Misses: 3 Evictions: Zero Overflows: 0
-> Index Scan using strtest_t_idx on strtest s2 (actual rows=4 loops=N)
Index Cond: (t <= s1.t)
(8 rows)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7555764c77..cabadd48b8 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2479,7 +2479,6 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
- Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
@@ -2494,14 +2493,13 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
- Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-(34 rows)
+(32 rows)
table ab;
a | b
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 4ea1aa7dfd..9f36c62741 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -562,24 +562,11 @@ explain (analyze, timing off, summary off, costs off)
alter table tenk2 reset (parallel_workers);
reset work_mem;
-create function explain_parallel_sort_stats() returns setof text
-language plpgsql as
-$$
-declare ln text;
-begin
- for ln in
- explain (analyze, timing off, summary off, costs off)
- select * from
+explain (analyze)
+select * from
(select ten from tenk1 where ten < 100 order by ten) ss
- right join (values (1),(2),(3)) v(x) on true
- loop
- ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
- return next ln;
- end loop;
-end;
-$$;
-select * from explain_parallel_sort_stats();
- explain_parallel_sort_stats
+ right join (values (1),(2),(3)) v(x) on true;
+ QUERY PLAN
--------------------------------------------------------------------------
Nested Loop Left Join (actual rows=30000 loops=1)
-> Values Scan on "*VALUES*" (actual rows=3 loops=1)
@@ -588,11 +575,11 @@ select * from explain_parallel_sort_stats();
Workers Launched: 4
-> Sort (actual rows=2000 loops=15)
Sort Key: tenk1.ten
- Sort Method: quicksort Memory: xxx
- Worker 0: Sort Method: quicksort Memory: xxx
- Worker 1: Sort Method: quicksort Memory: xxx
- Worker 2: Sort Method: quicksort Memory: xxx
- Worker 3: Sort Method: quicksort Memory: xxx
+ Sort Method: quicksort
+ Worker 0: Sort Method: quicksort
+ Worker 1: Sort Method: quicksort
+ Worker 2: Sort Method: quicksort
+ Worker 3: Sort Method: quicksort
-> Parallel Seq Scan on tenk1 (actual rows=2000 loops=15)
Filter: (ten < 100)
(14 rows)
@@ -603,7 +590,6 @@ reset enable_mergejoin;
reset enable_material;
reset effective_io_concurrency;
drop table bmscantest;
-drop function explain_parallel_sort_stats();
-- test parallel merge join path.
set enable_hashjoin to off;
set enable_nestloop to off;
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 4e8ddc7061..199badece3 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1527,27 +1527,15 @@ insert into sq_limit values
(6, 2, 2),
(7, 3, 3),
(8, 4, 4);
-create function explain_sq_limit() returns setof text language plpgsql as
-$$
-declare ln text;
-begin
- for ln in
- explain (analyze, summary off, timing off, costs off)
- select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
- loop
- ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
- return next ln;
- end loop;
-end;
-$$;
-select * from explain_sq_limit();
- explain_sq_limit
+explain (analyze)
+ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
+ QUERY PLAN
----------------------------------------------------------------
Limit (actual rows=3 loops=1)
-> Subquery Scan on x (actual rows=3 loops=1)
-> Sort (actual rows=3 loops=1)
Sort Key: sq_limit.c1, sq_limit.pk
- Sort Method: top-N heapsort Memory: xxx
+ Sort Method: top-N heapsort
-> Seq Scan on sq_limit (actual rows=8 loops=1)
(6 rows)
@@ -1559,7 +1547,6 @@ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
2 | 2
(3 rows)
-drop function explain_sq_limit();
drop table sq_limit;
--
-- Ensure that backward scan direction isn't propagated into
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index f91d1004d5..c38857d346 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -72,6 +72,23 @@ select explain_filter('explain (analyze, buffers, format yaml) select * from int
select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
+-- HashAgg
+begin;
+SET work_mem='64kB';
+select explain_filter('explain (analyze) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+select explain_filter('explain (analyze, machine off) SELECT a, COUNT(1) FROM generate_series(1,9999)a GROUP BY 1');
+rollback;
+
+-- Bitmap scan
+begin;
+SET enable_indexscan=no;
+CREATE TABLE explainbitmap AS SELECT i AS a FROM generate_series(1,999) AS i;
+ANALYZE explainbitmap;
+CREATE INDEX ON explainbitmap(a);
+select explain_filter('explain (analyze) SELECT * FROM explainbitmap WHERE a<9');
+select explain_filter('explain (analyze, machine off) SELECT * FROM explainbitmap WHERE a<9');
+rollback;
+
-- SETTINGS option
-- We have to ignore other settings that might be imposed by the environment,
-- so printing the whole Settings field unfortunately won't do.
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index c6ed5a2aa6..1368f8dd18 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -22,9 +22,7 @@ begin
end if;
ln := regexp_replace(ln, 'Evictions: 0', 'Evictions: Zero');
ln := regexp_replace(ln, 'Evictions: \d+', 'Evictions: N');
- ln := regexp_replace(ln, 'Memory Usage: \d+', 'Memory Usage: N');
- ln := regexp_replace(ln, 'Heap Fetches: \d+', 'Heap Fetches: N');
- ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
+ ln := regexp_replace(ln, 'loops=\d+', 'loops=N');
return next ln;
end loop;
end;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index f924731248..fc58616123 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -221,23 +221,10 @@ explain (analyze, timing off, summary off, costs off)
alter table tenk2 reset (parallel_workers);
reset work_mem;
-create function explain_parallel_sort_stats() returns setof text
-language plpgsql as
-$$
-declare ln text;
-begin
- for ln in
- explain (analyze, timing off, summary off, costs off)
- select * from
+explain (analyze)
+select * from
(select ten from tenk1 where ten < 100 order by ten) ss
- right join (values (1),(2),(3)) v(x) on true
- loop
- ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
- return next ln;
- end loop;
-end;
-$$;
-select * from explain_parallel_sort_stats();
+ right join (values (1),(2),(3)) v(x) on true;
reset enable_indexscan;
reset enable_hashjoin;
@@ -245,7 +232,6 @@ reset enable_mergejoin;
reset enable_material;
reset effective_io_concurrency;
drop table bmscantest;
-drop function explain_parallel_sort_stats();
-- test parallel merge join path.
set enable_hashjoin to off;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index e879999708..82285250e7 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -797,26 +797,11 @@ insert into sq_limit values
(7, 3, 3),
(8, 4, 4);
-create function explain_sq_limit() returns setof text language plpgsql as
-$$
-declare ln text;
-begin
- for ln in
- explain (analyze, summary off, timing off, costs off)
- select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3
- loop
- ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
- return next ln;
- end loop;
-end;
-$$;
-
-select * from explain_sq_limit();
+explain (analyze)
+ select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3;
-drop function explain_sq_limit();
-
drop table sq_limit;
--
--
2.17.0
[text/x-diff] 0005-f-Rows-removed-by-filter.patch (18.4K, ../../20211202005820.GJ17618@telsasoft.com/6-0005-f-Rows-removed-by-filter.patch)
download | inline diff:
From 093713b6a792fad4a85affb6e612117002d939ad Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 16 Nov 2021 11:22:40 -0600
Subject: [PATCH 05/11] f!Rows removed by filter
This cleans one more kludge in partition_prune, but drags in 2 more files...
---
.../postgres_fdw/expected/postgres_fdw.out | 6 ++--
src/backend/commands/explain.c | 36 +++++++++----------
src/test/regress/expected/memoize.out | 9 ++---
src/test/regress/expected/partition_prune.out | 29 +++++----------
src/test/regress/expected/select_parallel.out | 4 +--
src/test/regress/sql/partition_prune.sql | 1 -
6 files changed, 32 insertions(+), 53 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 5196e4797a..43f0b98432 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10493,13 +10493,12 @@ SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c
Nested Loop (actual rows=1 loops=1)
-> Seq Scan on local_tbl (actual rows=1 loops=1)
Filter: (c = 'bar'::text)
- Rows Removed by Filter: 1
-> Append (actual rows=1 loops=1)
-> Async Foreign Scan on async_p1 async_pt_1 (never executed)
-> Async Foreign Scan on async_p2 async_pt_2 (actual rows=1 loops=1)
-> Seq Scan on async_p3 async_pt_3 (never executed)
Filter: (local_tbl.a = a)
-(9 rows)
+(8 rows)
SELECT * FROM local_tbl, async_pt WHERE local_tbl.a = async_pt.a AND local_tbl.c = 'bar';
a | b | c | a | b | c
@@ -10633,8 +10632,7 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
Filter: (b === 505)
-> Seq Scan on async_p3 t1_3 (actual rows=1 loops=1)
Filter: (b === 505)
- Rows Removed by Filter: 101
-(9 rows)
+(8 rows)
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
a | b | c
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 833daac940..9ef21d1044 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1765,7 +1765,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_scan_qual(((IndexScan *) plan)->indexorderbyorig,
"Order By", planstate, ancestors, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
@@ -1778,7 +1778,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_scan_qual(((IndexOnlyScan *) plan)->indexorderby,
"Order By", planstate, ancestors, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
if (es->analyze && es->machine)
@@ -1796,7 +1796,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_instrumentation_count("Rows Removed by Index Recheck", 2,
planstate, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
if (es->analyze)
@@ -1814,7 +1814,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_WorkTableScan:
case T_SubqueryScan:
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
@@ -1823,7 +1823,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
Gather *gather = (Gather *) plan;
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
ExplainPropertyInteger("Workers Planned", NULL,
@@ -1851,7 +1851,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
GatherMerge *gm = (GatherMerge *) plan;
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
ExplainPropertyInteger("Workers Planned", NULL,
@@ -1889,7 +1889,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
es->verbose, es);
}
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
@@ -1903,7 +1903,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
es->verbose, es);
}
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
@@ -1919,7 +1919,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
tidquals = list_make1(make_orclause(tidquals));
show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
}
@@ -1936,14 +1936,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
tidquals = list_make1(make_andclause(tidquals));
show_scan_qual(tidquals, "TID Cond", planstate, ancestors, es);
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
}
break;
case T_ForeignScan:
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
show_foreignscan_info((ForeignScanState *) planstate, es);
@@ -1953,7 +1953,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
CustomScanState *css = (CustomScanState *) planstate;
show_scan_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
if (css->methods->ExplainCustomScan)
@@ -1967,7 +1967,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_instrumentation_count("Rows Removed by Join Filter", 1,
planstate, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 2,
planstate, es);
break;
@@ -1980,7 +1980,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_instrumentation_count("Rows Removed by Join Filter", 1,
planstate, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 2,
planstate, es);
break;
@@ -1993,7 +1993,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_instrumentation_count("Rows Removed by Join Filter", 1,
planstate, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 2,
planstate, es);
break;
@@ -2001,14 +2001,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_agg_keys(castNode(AggState, planstate), ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
show_hashagg_info((AggState *) planstate, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
case T_Group:
show_group_keys(castNode(GroupState, planstate), ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
@@ -2030,7 +2030,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
show_upper_qual((List *) ((Result *) plan)->resconstantqual,
"One-Time Filter", planstate, ancestors, es);
show_upper_qual(plan->qual, "Filter", planstate, ancestors, es);
- if (plan->qual)
+ if (plan->qual && es->machine)
show_instrumentation_count("Rows Removed by Filter", 1,
planstate, es);
break;
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index fbe6bed433..3e521f3487 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -39,14 +39,13 @@ WHERE t2.unique1 < 1000;', false);
-> Nested Loop (actual rows=1000 loops=N)
-> Seq Scan on tenk1 t2 (actual rows=1000 loops=N)
Filter: (unique1 < 1000)
- Rows Removed by Filter: 9000
-> Memoize (actual rows=1 loops=N)
Cache Key: t2.twenty
Cache Mode: logical
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.twenty)
-(11 rows)
+(10 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1
@@ -68,14 +67,13 @@ WHERE t1.unique1 < 1000;', false);
-> Nested Loop (actual rows=1000 loops=N)
-> Seq Scan on tenk1 t1 (actual rows=1000 loops=N)
Filter: (unique1 < 1000)
- Rows Removed by Filter: 9000
-> Memoize (actual rows=1 loops=N)
Cache Key: t1.twenty
Cache Mode: logical
Hits: 980 Misses: 20 Evictions: Zero Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)
Index Cond: (unique1 = t1.twenty)
-(11 rows)
+(10 rows)
-- And check we get the expected results.
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
@@ -102,14 +100,13 @@ WHERE t2.unique1 < 1200;', true);
-> Nested Loop (actual rows=1200 loops=N)
-> Seq Scan on tenk1 t2 (actual rows=1200 loops=N)
Filter: (unique1 < 1200)
- Rows Removed by Filter: 8800
-> Memoize (actual rows=1 loops=N)
Cache Key: t2.thousand
Cache Mode: logical
Hits: N Misses: N Evictions: N Overflows: 0
-> Index Only Scan using tenk1_unique1 on tenk1 t1 (actual rows=1 loops=N)
Index Cond: (unique1 = t2.thousand)
-(11 rows)
+(10 rows)
CREATE TABLE flt (f float);
CREATE INDEX flt_f_idx ON flt (f);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index cabadd48b8..3576e65bc2 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1922,17 +1922,13 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh
Append (actual rows=0 loops=1)
-> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
-> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
-> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
-> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1)
Filter: (a = (list_part_fn(1) + a))
- Rows Removed by Filter: 1
-(13 rows)
+(9 rows)
rollback;
drop table list_part;
@@ -1957,7 +1953,6 @@ begin
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
- ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
return next ln;
end loop;
end;
@@ -2196,7 +2191,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
-> Nested Loop (actual rows=N loops=N)
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
Filter: (a = ANY ('{1,0,0}'::integer[]))
- Rows Removed by Filter: N
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
Index Cond: (a = a.a)
@@ -2216,7 +2210,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
Index Cond: (a = a.a)
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
Index Cond: (a = a.a)
-(28 rows)
+(27 rows)
delete from lprt_a where a = 1;
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
@@ -2230,7 +2224,6 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
-> Nested Loop (actual rows=N loops=N)
-> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
Filter: (a = ANY ('{1,0,0}'::integer[]))
- Rows Removed by Filter: N
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed)
Index Cond: (a = a.a)
@@ -2250,7 +2243,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
Index Cond: (a = a.a)
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
Index Cond: (a = a.a)
-(28 rows)
+(27 rows)
reset enable_hashjoin;
reset enable_mergejoin;
@@ -2437,14 +2430,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on xy_1 (actual rows=0 loops=1)
Filter: ((x = $1) AND (y = $0))
- Rows Removed by Filter: 1
-> Seq Scan on ab_a1_b1 ab_4 (never executed)
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on ab_a1_b2 ab_5 (never executed)
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on ab_a1_b3 ab_6 (never executed)
Filter: ((a = $1) AND (b = $0))
-(19 rows)
+(18 rows)
-- Ensure we see just the xy_1 row.
execute ab_q6(100);
@@ -3052,12 +3044,11 @@ select * from boolp where a = (select value from boolvalues where value);
InitPlan 1 (returns $0)
-> Seq Scan on boolvalues (actual rows=1 loops=1)
Filter: value
- Rows Removed by Filter: 1
-> Seq Scan on boolp_f boolp_1 (never executed)
Filter: (a = $0)
-> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1)
Filter: (a = $0)
-(9 rows)
+(8 rows)
explain (analyze, costs off, summary off, timing off)
select * from boolp where a = (select value from boolvalues where not value);
@@ -3067,12 +3058,11 @@ select * from boolp where a = (select value from boolvalues where not value);
InitPlan 1 (returns $0)
-> Seq Scan on boolvalues (actual rows=1 loops=1)
Filter: (NOT value)
- Rows Removed by Filter: 1
-> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1)
Filter: (a = $0)
-> Seq Scan on boolp_t boolp_2 (never executed)
Filter: (a = $0)
-(9 rows)
+(8 rows)
drop table boolp;
--
@@ -3096,11 +3086,9 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
Subplans Removed: 1
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
- Rows Removed by Filter: 9
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
- Rows Removed by Filter: 9
-(9 rows)
+(7 rows)
execute mt_q1(15);
a
@@ -3117,8 +3105,7 @@ explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
Subplans Removed: 2
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
- Rows Removed by Filter: 9
-(6 rows)
+(5 rows)
execute mt_q1(25);
a
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 9f36c62741..02176d7e2f 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -551,14 +551,12 @@ explain (analyze, timing off, summary off, costs off)
-> Nested Loop (actual rows=98000 loops=1)
-> Seq Scan on tenk2 (actual rows=10 loops=1)
Filter: (thousand = 0)
- Rows Removed by Filter: 9990
-> Gather (actual rows=9800 loops=10)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50)
Filter: (hundred > 1)
- Rows Removed by Filter: 40
-(11 rows)
+(9 rows)
alter table tenk2 reset (parallel_workers);
reset work_mem;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d70bd8610c..e3938bea9c 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -463,7 +463,6 @@ begin
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
- ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
return next ln;
end loop;
end;
--
2.17.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-11-15 19:09 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Justin Pryzby <pryzby@telsasoft.com>
2021-12-02 00:58 ` Re: BUFFERS enabled by default in EXPLAIN (ANALYZE) Justin Pryzby <pryzby@telsasoft.com>
@ 2022-01-18 06:53 ` Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2022-01-18 06:53 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Nikolay Samokhvalov <samokhvalov@gmail.com>; pgsql-hackers@lists.postgresql.org
Hi,
On Wed, Dec 01, 2021 at 06:58:20PM -0600, Justin Pryzby wrote:
>
> The attached patch series now looks like this (some minor patches are not
> included in this list):
This version of the patchset doesn't apply anymore:
http://cfbot.cputube.org/patch_36_3409.log
=== Applying patches on top of PostgreSQL commit ID 4483b2cf29bfe8091b721756928ccbe31c5c8e14 ===
=== applying patch ./0003-Make-explain-default-to-BUFFERS-TRUE.patch
patching file doc/src/sgml/config.sgml
Hunk #1 FAILED at 7615.
1 out of 1 hunk FAILED -- saving rejects to file doc/src/sgml/config.sgml.rej
By the way, could you add versionning to the patchsets you send, e.g. git
format-patch -vXXX. It would make it easier to know which version are used or
discussed.
I'm switching the CF entry to Waiting on Author.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-01-18 06:53 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-12 22:58 BUFFERS enabled by default in EXPLAIN (ANALYZE) Nikolay Samokhvalov <samokhvalov@gmail.com>
2021-11-13 00:18 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2021-11-13 04:17 ` Julien Rouhaud <rjuju123@gmail.com>
2021-11-13 01:34 ` Vik Fearing <vik@postgresfriends.org>
2021-11-15 19:09 ` Justin Pryzby <pryzby@telsasoft.com>
2021-11-24 18:35 ` Michael Christofides <michael@pgmustard.com>
2021-12-02 00:58 ` Justin Pryzby <pryzby@telsasoft.com>
2022-01-18 06:53 ` Julien Rouhaud <rjuju123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox