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

On Mon, Nov 11, 2024 at 3:59 PM Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> Agreed. Having an "EXPLAIN (ALL)" would be a great addition. I could tell a customer to do an "EXPLAIN (ALL)", rather
thanfirst asking the PostgreSQL release installed on the server and after that, giving the correct options for EXPLAIN. 

I realize that you're probably going to hate my guts -- or hate them
even more than you do already -- but I doubt that a proposal to add
EXPLAIN (ALL) will go anywhere. The definitional problem is that it is
not clear what to do with non-Boolean valued options, such as
SERIALIZE. People who think that we were wrong not to make SERIALIZE
TEXT the default in v17 will argue that EXPLAIN (ALL) should turn it
on; after all, the backward-compatibility argument carries no water in
that case. But people who do not like the behavior of SERIALIZE TEXT
will not be happy about that. They might directly make that argument,
or they might instead make the argument that ALL should do nothing
about a non-Boolean valued option. But that position is really quite
difficult to justify. Let's suppose that the current BUFFERS option,
which is Boolean, got replaced with BUFFERS { detailed | on | off }.
Well, then, by the principle that ALL only affects Boolean-valued
options, it's no longer included in EXPLAIN (ALL). Nobody will be
happy with that. Practically speaking, I think it will be very
difficult to get agreement on what EXPLAIN (ALL) should do, and I
think it is unlikely that anything will get committed no matter how
much time we spend arguing about it.

But I think we would get most of the same benefit from just doing what
David Rowley proposed and turning on EXPLAIN (BUFFERS) by default. I'd
suggest that we decide that, without ANALYZE, the option would not do
anything; that is already how TIMING works. So this would be a very
small patch and would probably get a lot of support from a lot of
people. It also wouldn't require users to change their habits or learn
any new syntax -- they could just keep typing EXPLAIN ANALYZE or
EXPLAIN ANALYZE VERBOSE and all would be well.

And the same principle could be applied to other EXPLAIN options if
there is sufficient consensus. We could default to WAL ON, SERIALIZE
TEXT, and MEMORY ON, if we wanted to do that. However, the more we try
to change at once, the less likely it is that anything will happen at
all. For example, I personally believe that EXPLAIN (MEMORY) should be
ripped out of the server as both badly-named and mostly useless, so
I'm not going to vote in favor of turning it on by default; and I
wouldn't vote for enabling WAL by default because I have no experience
with it to suggest that it's routinely valuable and thus worth the
overhead. I would vote for SERIALIZE TEXT because I've seen that cause
gross distortion of EXPLAIN ANALYZE results on many occasions. But the
point is that other people will vote differently, so tying all the
proposals together just increases the chances of agreeing on nothing
at all.

So to recap: everyone is free to propose whatever they like, and I am
not in charge here, but if you want to get something committed, the
proposal which I think has the highest chance of success is: propose
to make BUFFERS ON the default (but a noop without ANALYZE, similar to
how TIMING already works).

--
Robert Haas
EDB: http://www.enterprisedb.com