Subject: BUFFERS enabled by default in EXPLAIN (ANALYZE)

From: Nikolay Samokhvalov <[email-masked]>
Date: Fri, 12 Nov 2021 14:58:07 -0800

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.org
[2] https://commitfest.postgresql.org/28/2567/

---

From: Tomas Vondra <[email-masked]>
Date: Sat, 13 Nov 2021 01:18:31 +0100

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

---

From: Vik Fearing <[email-masked]>
Date: Sat, 13 Nov 2021 02:34:05 +0100

> Attached is a simple patch enabling BUFFERS by default, without involving

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

---

From: Julien Rouhaud <[email-masked]>
Date: Sat, 13 Nov 2021 12:17:58 +0800

> (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.

---

From: Justin Pryzby <[email-masked]>
Date: Mon, 15 Nov 2021 13:09:54 -0600

> check the behavior of BUFFERS (such as both default options – with and

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/[email-masked]

> people understand why planning time is so big, in some cases.

I think it *should* be enabled for planning, since that makes the default
easier to understand and document, and it makes a user's use of "explain"
easier.

However, that makes this patch series more complicated: explain(ANALYZE) is
rare in the regression tests, but this would affect the output of bare
"explain", which affects almost every test.

I think the answer to that is to add a GUC (as Tom suggested in an old thread)
like explain_regress=on, which causes explain to omit these details.  This
would be instead of my explain(REGRESS), and would change the defaults of
various params in a central place, to avoid the need to update many regression
tests.

In the future, this might also handle the stuff that my "explain(MACHINE)"
attempted to handle.

I've rebased my patches like this.  I think the explain(REGRESS) should be
re-written as a GUC which is set by regress.c.  I'm interested to hear from a
reviewer if this is worth pursing like this.

-- 
Justin

---

From: Michael Christofides <[email-masked]>
Date: Wed, 24 Nov 2021 18:35:45 +0000

> easier to understand and document, and it makes a user's use of "explain"

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

---

From: Justin Pryzby <[email-masked]>
Date: Wed, 1 Dec 2021 18:58:20 -0600

> https://www.postgresql.org/message-id/flat/[email-masked]

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.