public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nikolay Samokhvalov <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: BUFFERS enabled by default in EXPLAIN (ANALYZE)
Date: Fri, 12 Nov 2021 14:58:07 -0800
Message-ID: <CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw@mail.gmail.com> (raw)
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 <[email protected]>
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
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)
In-Reply-To: <CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox