Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE - Mailing list pgsql-hackers

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@mail.gmail.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@endpoint.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.