Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE

On Tue, Nov 5, 2024 at 10:30 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> we're not working in a green field here, and all these
> decisions have history.
>

I hear you and understand.

Ready to do legwork here.

1. VERBOSE first appeared in 1997 in 6.3 in 3a02ccfa, with different
meaning:

> This command [EXPLAIN] outputs details about the supplied query. The
default
> output is the computed query cost. \f2verbose\f1 displays the full query
> plan and cost.

2. Support for parenthesis was added in d4382c4a (2009, 8.5), with "test"
option COSTS, and this opened gates to extending with many options.

3. BUFFERS was added in d4382c4 (also 2009, 8.5), discussion
https://www.postgresql.org/message-id/flat/4AC12A17.5040305%40timbira.com,
I didn't see that inclusion it to VERBOSE was discussed.

In my opinion, this option is invaluable: most of the performance
optimization is done by reducing IO so seeing these numbers helps make
decisions much faster. I always use them. When you optimize and, for
example, want to verify an index idea, it's not good to do it on production
– it's better to work with clones. There, we can have weaker hardware,
different buffer state, etc. So timing numbers might be really off. Timing
can be different even on the same server, e.g. after restart, when buffer
pool is not warmed up. But BUFFERS never lie – they are not affected by
saturated CPU if it happens, lock acquisition waits, etc. Not looking at
them is missing an essential part of analysis, I strongly believe.

It looks like in 2009, when the BUFFERS option was created, it was not
enough understanding that it is so useful, so it was not discussed to
include them by default or at least – as we discuss here – to involve in
VERBOSE.

I want to emphasize: BUFFERS is essential in my work and more and more
people are convinced that during the optimization process, when you're
inside it, in most cases it's beneficial to focus on BUFFERS. Notice that
explain.depesz.com, explain.dalibo.com, pgMustard and many tools recognize
it and ask users to include BUFFERS to analysis. And see the next item:

4. Making BUFFERS default behavior for EXPLAIN ANALYZE was raised several
times, for example
https://www.postgresql.org/message-id/flat/CANNMO++=LrJ4upoeydZhbmpd_ZgZjrTLueKSrivn6xmb=yFwQw(at)mail(dot)gmail(dot)com
(2021) – and my understanding that it was received great support and it
discussed in detail why it's useful, but then several attempts to implement
it were not accomplished because of tech difficulties (as I remember,
problem with broken tests and how to fix that).

5. EXPLAIN ALL proposed in
https://www.postgresql.org/message-id/flat/080FE841-E38D-42A9-AD6D-48CABED163C9(at)endpoint(dot)com
(2016) – I think it's actually a good idea originally, but didn't survive
questions of mutually exclusive options and non-binary options, and then
discussion stopped after pivoting in direction of GUC.

6. FInally, the fresh SERIALIZE option was discussed in
https://www.postgresql.org/message-id/flat/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1%40gmx.de
(2023-2024, 17), and unfortunately again.

I might be missing some discussions – please help me find them; I also
expect that there are many people who support me thinking that BUFFERS are
very useful and should be default or at least inside VERBOSE. Meanwhile:
- to be able to have all data in hand during analysis, we need to recommend
users to collect plans using EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS),
which looks really long
- independently, I know see pgMustard ended up having a similar
recommendation: https://www.pgmustard.com/getting-a-query-plan:
> For better advice, we recommend using at least: explain (analyze,
format json, buffers, verbose, settings)

My proposal remains: EXPLAIN ANALYZE VERBOSE -- let's consider this, please.