Thread: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
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?
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?
3) small thing about grammar: allow omitting parentheses, so EXPLAIN EXECUTE VERBOSE would work.
if both changes are done, we could use EXPLAIN (EXECUTE, VERBOSE) to be able to collect data in a great way for analysis.
have a really nice week,
Nik
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
Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
From
Nikolay Samokhvalov
Date:
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_:
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 Postgresusers: > > 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 themso 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
Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
From
Nikolay Samokhvalov
Date:
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
Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
From
Nikolay Samokhvalov
Date:
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 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.
Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
From
Nikolay Samokhvalov
Date:
On Tue, Nov 5, 2024 at 2:54 PM Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
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.
(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.
Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
From
"David G. Johnston"
Date:
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 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).
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 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.
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 https://postgr.es/m/CA+TgmoYH_p-y=45SAJ58cU6jsMH6ojgqQZiA2aePpvZ0J+uLbA@mail.gmail.com David
Re: Proposals for EXPLAIN: rename ANALYZE to EXECUTE and extend VERBOSE
From
Michael Christofides
Date:
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.
> (2023-2024, 17)
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
> https://www.postgresql.org/message-id/flat/ca0adb0e-fa4e-c37e-1cd7-91170b18cae1%40gmx.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.
—
Michael Christofides
Founder, pgMustard
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.