Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE

Nikolay Samokhvalov <samokhvalov@gmail.com> writes:

1) EXPLAIN ANALYZE Is sometimes very confusing (because there is ANALYZE).
Let's rename it to EXPLAIN EXECUTE?

This has got far too many years of history to be renamed now.

2) VERBOSE doesn't include BUFFERS, and doesn't include SETTINGS; it might
be also confusing sometimes. Let's include them so VERBOSE would be really
verbose?

This is not likely to fly for compatibility reasons.

3) small thing about grammar: allow omitting parentheses, so EXPLAIN
EXECUTE VERBOSE would work.

The reason for the parens is that the other way would require reserving
all these options as keywords.

regards, tom lane

On Tue, Nov 5, 2024 at 10:16 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nikolay Samokhvalov <samokhvalov@gmail.com> writes:

1) EXPLAIN ANALYZE Is sometimes very confusing (because there is

ANALYZE).

Let's rename it to EXPLAIN EXECUTE?

This has got far too many years of history to be renamed now.

This is a really, really strange argument. Postgres keeps receiving new
audiences at larger and larger scale. And they are confused.

It's better late than never. I didn't believe we would have "quit" working
in psql.

2) VERBOSE doesn't include BUFFERS, and doesn't include SETTINGS; it

might

be also confusing sometimes. Let's include them so VERBOSE would be

really

verbose?

This is not likely to fly for compatibility reasons.

Can you elaborate?

3) small thing about grammar: allow omitting parentheses, so EXPLAIN
EXECUTE VERBOSE would work.

The reason for the parens is that the other way would require reserving
all these options as keywords.

turns out, EXPLAIN ANALYZE VERBOSE already working (it's just not as
verbose as one might expect_:

test=# explain analyze verbose select;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
(1 row)

On Tue, Nov 5, 2024 at 1:02 PM Nikolay Samokhvalov
<samokhvalov@gmail.com> wrote:

hi, I have a proposal, resulted from numerous communications with various folks, both very experienced and new Postgres users:

1) EXPLAIN ANALYZE Is sometimes very confusing (because there is ANALYZE). Let's rename it to EXPLAIN EXECUTE?

The trouble is that EXPLAIN EXECUTE already means something.

robert.haas=# explain execute foo;
ERROR: prepared statement "foo" does not exist

Granted, that would not make it impossible to make EXPLAIN (EXECUTE) a
synonym for EXPLAIN (ANALYZE), but IMHO it would be pretty confusing
if EXPLAIN EXECUTE and EXPLAIN (EXECUTE) did different things.

2) VERBOSE doesn't include BUFFERS, and doesn't include SETTINGS; it might be also confusing sometimes. Let's include them so VERBOSE would be really verbose?

I agree that the naming here isn't great, but I think making the
options non-orthogonal would probably be worse.

3) small thing about grammar: allow omitting parentheses, so EXPLAIN EXECUTE VERBOSE would work.

Perhaps surprisingly, it turns out that this is not a small change. As
Tom mentions, this would have a pretty large blast radius. In fact,
the reason I wrote the patch to introduce parenthesized options for
EXPLAIN was precisely because the unparenthesized option syntax does
not scale nicely at all.

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

On Tue, Nov 5, 2024 at 10:19 AM Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Nov 5, 2024 at 1:02 PM Nikolay Samokhvalov
<samokhvalov@gmail.com> wrote:

hi, I have a proposal, resulted from numerous communications with

various folks, both very experienced and new Postgres users:

1) EXPLAIN ANALYZE Is sometimes very confusing (because there is

ANALYZE). Let's rename it to EXPLAIN EXECUTE?

The trouble is that EXPLAIN EXECUTE already means something.

robert.haas=# explain execute foo;
ERROR: prepared statement "foo" does not exist

Granted, that would not make it impossible to make EXPLAIN (EXECUTE) a
synonym for EXPLAIN (ANALYZE), but IMHO it would be pretty confusing
if EXPLAIN EXECUTE and EXPLAIN (EXECUTE) did different things.

2) VERBOSE doesn't include BUFFERS, and doesn't include SETTINGS; it

might be also confusing sometimes. Let's include them so VERBOSE would be
really verbose?

I agree that the naming here isn't great, but I think making the
options non-orthogonal would probably be worse.

3) small thing about grammar: allow omitting parentheses, so EXPLAIN

EXECUTE VERBOSE would work.

Perhaps surprisingly, it turns out that this is not a small change. As
Tom mentions, this would have a pretty large blast radius. In fact,
the reason I wrote the patch to introduce parenthesized options for
EXPLAIN was precisely because the unparenthesized option syntax does
not scale nicely at all.

I appreciate all yours and Tom's very quick comments here!

Item 3 is already solved, as it turned out.

Let's focus on item 2. Is it really impossible to make VERBOSE really
verbose?

Nikolay Samokhvalov <samokhvalov@gmail.com> writes:

Let's focus on item 2. Is it really impossible to make VERBOSE really
verbose?

It's obviously not "impossible" -- the code changes would likely be
trivial. The question is whether it's a good idea. These semantics
were (I presume) deliberately chosen when the options were added,
so somebody thought not. You would need to go back and review the
relevant mail thread and then make arguments why that decision
was wrong.

In short: we're not working in a green field here, and all these
decisions have history. You will not get far by just popping
up and saying "I think it should be different". You need to make
a case why the decision was wrong, and why it was so wrong that
we should risk cross-version-compatibility problems by changing.

regards, tom lane

On Tue, Nov 5, 2024 at 1:24 PM Nikolay Samokhvalov
<samokhvalov@gmail.com> wrote:

Item 3 is already solved, as it turned out.

ANALYZE and VERBOSE are treated specially because those options
existed prior to the parenthesized syntax. Scaling that treatment to a
large number of options will not work out.

Let's focus on item 2. Is it really impossible to make VERBOSE really verbose?

It is, of course, not impossible. But the fact that something is
possible does not necessarily mean that it is a good idea. I think it
can be quite confusing when the same behavior is controlled in more
than one way. If the VERBOSE option turns information about BUFFERS on
and off, and the BUFFERS option does the same thing, what happens if I
say EXPLAIN (VERBOSE ON, BUFFERS OFF)? Is it different if I say
EXPLAIN (BUFFERS OFF, VERBOSE ON)? There's a lot of opportunity for
the behavior to be confusing here. Then, too, we can argue about what
should be included in VERBOSE. You propose BUFFERS and SETTINGS, but
we've also got SERIALIZE (which is not even Boolean-valued), WAL, and
MEMORY. One can argue that we ought to include everything when VERBOSE
is specified; one can also argue that some of this stuff is too
marginal and too high-overhead to justify its inclusion. Both
arguments have merit, IMHO.

I'm not very happy with the current situation. I agree that EXPLAIN
has gotten a bit too complicated. However, I also know that not
everyone wants the same things. And I can say from a PostgreSQL
support perspective that I do not always want a customer to just "turn
on everything", as EXPLAIN output can be extremely long and adding a
whole bunch of additional details that make already-long output even
longer can easily be actively unhelpful. For me personally, just plain
EXPLAIN ANALYZE is usually enough. Sometimes I need VERBOSE to see the
target lists at each level, and very occasionally I need BUFFERS to
see how much data is being accessed, but at least for me, those are
pretty rare cases. So I don't think I really believe the "everybody
always wants that" argument. One of the most common things that I have
to do with EXPLAIN output is trim the small amounts of relevant
material out of the giant pile of things that don't matter to the
problem at hand. If you enable an option that adds an extra line of
output for every node and there are 100 nodes in the query plan, that
is a whole lot of additional clutter.

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

On Tue, Nov 5, 2024 at 10:30 AM Tom Lane <tgl@sss.pgh.pa.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
/messages/by-id/4AC12A17.5040305@timbira.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
/messages/by-id/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
/messages/by-id/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
/messages/by-id/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.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.

On Tue, Nov 5, 2024 at 2:54 PM Nikolay Samokhvalov <samokhvalov@gmail.com>
wrote:

6. FInally, the fresh SERIALIZE option was discussed in
/messages/by-id/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.de
(2023-2024, 17), and unfortunately again.

(didn't finish the phrase here and hit Send)
...again, I don't see that it was discussed to include the SERIALIZE
behavior to VERBOSE. I don't use SERIALIZE myself, but during our podcasts,
Michael (CCing him) was wondering why it was so.

Summary: I haven't found explicit discussions of including new options to
VERBOSE, when that new options were created. I used Google, the .org
search, and postgres.ai semantic search over archives involving
pgvector/HNSW – I might be missing something, or it was really not
discussed when new options were added.

On Tue, Nov 5, 2024 at 3:55 PM Nikolay Samokhvalov <samokhvalov@gmail.com>
wrote:

4. Making BUFFERS default behavior for EXPLAIN ANALYZE was raised several
times, for example
/messages/by-id/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).

The main premise here is that explain should include buffers by default,
and to do so we are willing to inconvenience testers who do not want buffer
data in their test plans to have to modify their tests to explicitly
exclude buffers. We'll have to eat our own dog food here and go and add
"buffers off" throughout our code base to make this happen. I personally
feel that we should accept a patch that does so. The benefits to the many
outweigh the one-time inconveniencing of the few. Especially if limited to
explain analyze.

5. EXPLAIN ALL proposed in
/messages/by-id/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.

If the desire is to make the current keyword VERBOSE behave like the
proposed ALL keyword then one must first get a version of ALL accepted,
then argue for repurposing VERBOSE instead of adding the new keyword. But
at this point I really do not see extending verbose to mean more than "add
more comments and context labels". Verbose has never meant to include
everything and getting buy-in to change that seems highly unlikely.

In short, neither change is deemed unwanted, and indeed has desire. It's a
matter of learning from the previous attempt to increase the odds of
getting something committed.

I wouldn't advise expending effort or political capital on the parentheses
topic at this point.

David J.

On Tue, Nov 5, 2024 at 1:19 PM Nikolay Samokhvalov <samokhvalov@gmail.com>
wrote:

2) VERBOSE doesn't include BUFFERS, and doesn't include SETTINGS; it

might

be also confusing sometimes. Let's include them so VERBOSE would be

really

verbose?

This is not likely to fly for compatibility reasons.

Can you elaborate?

I am not sure about the compatibility reasons (other than backtesting, or
scripts?).

But, personally, as a relatively new person to PG, I was surprised that
VERBOSE did not include the buffers.
Could we somehow turn this on? (GUC: VERBOSE_INCLUDES_BUFFERS = yes/no)?

On Wed, 6 Nov 2024 at 13:14, Kirk Wolak <wolakk@gmail.com> wrote:

But, personally, as a relatively new person to PG, I was surprised that VERBOSE did not include the buffers.
Could we somehow turn this on? (GUC: VERBOSE_INCLUDES_BUFFERS = yes/no)?

Please read /messages/by-id/CA+TgmoYH_p-y=45SAJ58cU6jsMH6ojgqQZiA2aePpvZ0J+uLbA@mail.gmail.com

David

On Wed, 6 Nov 2024 at 12:33, David G. Johnston
<david.g.johnston@gmail.com> wrote:

The main premise here is that explain should include buffers by default, and to do so we are willing to inconvenience testers who do not want buffer data in their test plans to have to modify their tests to explicitly exclude buffers. We'll have to eat our own dog food here and go and add "buffers off" throughout our code base to make this happen. I personally feel that we should accept a patch that does so. The benefits to the many outweigh the one-time inconveniencing of the few. Especially if limited to explain analyze.

I'm not against analyze = on turning buffers on by default. However, I
think it would be quite painful to fix the tests if it were on without
analyze.

I tried it to see just how extensive the changes would need to be.
It's not too bad. partition_prune.sql is the worst hit.

23 files changed, 171 insertions(+), 166 deletions(-)

David

I'm not against analyze = on turning buffers on by default. However, I
think it would be quite painful to fix the tests if it were on without
analyze.

This would be amazing. I'm finding BUFFERS are especially helpful for
giving developers a clearer idea of why their queries are slow (especially
once converted to KB/MB/GB/TB).

The trouble is that EXPLAIN EXECUTE already means something.

I completely agree with this. So -1 from me on the first suggestion.

Let's focus on item 2.

+1 from me on item 2. I'd go further and have VERBOSE flip most other
parameters to on (or to their default for non-booleans), unless specified
otherwise. Specifically SUMMARY, BUFFERS, SETTINGS, WAL,
SERIALIZE (TEXT), and MEMORY. Although I do think it would be best if
BUFFERS and SERIALIZE were ON and TEXT by default respectively with
ANALYZE, which may reduce/remove the need for them to be affected by
VERBOSE.

If the VERBOSE option turns information about BUFFERS on
and off, and the BUFFERS option does the same thing, what happens if I
say EXPLAIN (VERBOSE ON, BUFFERS OFF)? Is it different if I say
EXPLAIN (BUFFERS OFF, VERBOSE ON)?

I'd expect this to work like other parameters that have dependencies, for
example both EXPLAIN (ANALYZE, SUMMARY OFF) and EXPLAIN
(SUMMARY OFF, ANALYZE) exclude the SUMMARY, even though it is
on by default with ANALYZE. So users could turn off anything they don't
want, if needed.

I'm not very happy with the current situation. I agree that EXPLAIN

has gotten a bit too complicated.

I agree. In the past 6 versions, 5 new parameters have been added.
SETTINGS in v12, WAL in v13, GENERIC_PLAN in v16, SERIALIZE in
v17, and MEMORY in v17. It feels like we should have some easier way
to get everything. Currently, we need to specify: EXPLAIN (ANALYZE,
VERBOSE, BUFFERS, SETTINGS, WAL, SERIALIZE, MEMORY).

If you enable an option that adds an extra line of
output for every node and there are 100 nodes in the query plan, that
is a whole lot of additional clutter.

This is a fair point, but I think it is worth it in the case of BUFFERS.
The
other parameter that adds a line per node is WAL, but the others don't
add much clutter.

Many people use tools these days to help read plans (I work on one,
so have some biased opinions of course). Tools help folks calculate
timings and spot bottlenecks , so once you're using a tool to read a plan,
more information is often beneficial for minimal overhead.

This is not likely to fly for compatibility reasons.

I'd be interested to hear more on this front too. One issue is that folks
with auto_explain.log_verbose = on would get extra output in their logs,
but I strongly suspect I'm missing some more important things.

the fresh SERIALIZE option was discussed in

/messages/by-id/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1@gmx.de

(2023-2024, 17)

I noticed in this thread Tom was against SERIALIZE being on by default
with ANALYZE, "because it would silently render EXPLAIN outputs from
different versions quite non-comparable." I'm not sure I agree with the
silently part, as the output from 17+ would include the serialization
details,
but again perhaps I'm missing something important.

Ready to do legwork here.

Same here.

—
Michael Christofides
Founder, pgMustard

On 05.11.24 19:19, Robert Haas wrote:

1) EXPLAIN ANALYZE Is sometimes very confusing (because there is ANALYZE). Let's rename it to EXPLAIN EXECUTE?

The trouble is that EXPLAIN EXECUTE already means something.

robert.haas=# explain execute foo;
ERROR: prepared statement "foo" does not exist

Granted, that would not make it impossible to make EXPLAIN (EXECUTE) a
synonym for EXPLAIN (ANALYZE), but IMHO it would be pretty confusing
if EXPLAIN EXECUTE and EXPLAIN (EXECUTE) did different things.

At some point in the past, the idea of renaming EXPLAIN ANALYZE to
PROFILE was thrown around. I still kind of like that idea. You'd have
to keep the existing syntax around, of course.

Hi,

Le mer. 6 nov. 2024 à 17:57, Michael Christofides <michael@pgmustard.com> a
écrit :

[...]
I agree. In the past 6 versions, 5 new parameters have been added.
SETTINGS in v12, WAL in v13, GENERIC_PLAN in v16, SERIALIZE in
v17, and MEMORY in v17. It feels like we should have some easier way
to get everything. Currently, we need to specify: EXPLAIN (ANALYZE,
VERBOSE, BUFFERS, SETTINGS, WAL, SERIALIZE, MEMORY).

Agreed. Having an "EXPLAIN (ALL)" would be a great addition. I could tell a
customer to do an "EXPLAIN (ALL)", rather than first asking the PostgreSQL
release installed on the server and after that, giving the correct options
for EXPLAIN.

--
Guillaume.

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 than first 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

Le mar. 12 nov. 2024 à 16:21, Robert Haas <robertmhaas@gmail.com> a écrit :

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 than first 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.

I don't hate your guts :) and...

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.

... I kinda agree with you. It would have been nice to have an "EXPLAIN
(ALL)" but I completely understand the issue.

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.

That would be a nice enhancement.

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.

Agreed.

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

Sounds like a plan.

Thanks.

--
Guillaume.

Le mar. 12 nov. 2024 à 16:35, Guillaume Lelarge <guillaume@lelarge.info> a
écrit :

Le mar. 12 nov. 2024 à 16:21, Robert Haas <robertmhaas@gmail.com> a
écrit :

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 than first 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.

I don't hate your guts :) and...

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.

... I kinda agree with you. It would have been nice to have an "EXPLAIN
(ALL)" but I completely understand the issue.

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.

That would be a nice enhancement.

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.

Agreed.

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

Sounds like a plan.

Sure looks easy enough to do (though it still lacks doc and tests changes).
See patch attached.

--
Guillaume.

On Tue, Nov 12, 2024 at 4:02 PM Guillaume Lelarge
<guillaume@lelarge.info> wrote:

Sure looks easy enough to do (though it still lacks doc and tests changes). See patch attached.

Yep, that's very small. I'm a bit wondering if it's too small, though.
standard_ExplainOneQuery() seems to do some stuff with es->buffers
even before it does planning, so if the idea is that this will be a
noop without ANALYZE, maybe this doesn't implement that. Also, you
should probably update the default value for auto_explain.log_buffers.
In general, I would recommend "git grep 'es->buffers'" and look
carefully at each place where it's mentioned and decide if anything
needs to be changed. And then change the stuff that needs it, and
include in your email an explanation of why the other things don't
need to be changed, unless it's obvious.

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