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

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

Same here.

—
Michael Christofides
Founder, pgMustard