Re: BUFFERS enabled by default in EXPLAIN (ANALYZE)

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(dot)GM684(at)telsasoft(dot)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.