New EXPLAIN option: ALL

On Tue, May 07, 2019 at 09:30:47AM +0200, David Fetter wrote:

Folks,

It can get a little tedious turning on (or off) all the boolean
options to EXPLAIN, so please find attached a shortcut.

Best,
David.

It helps to have a working patch for this.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi

I liked this idea.

+			timing_set = true;
+			es->timing = defGetBoolean(opt);
+			summary_set = true;
+			es->timing = defGetBoolean(opt);

second es->timing should be es->summary, right?

regards, Sergei

On Tue, 7 May 2019 at 09:30, David Fetter <david@fetter.org> wrote:

Folks,

It can get a little tedious turning on (or off) all the boolean
options to EXPLAIN, so please find attached a shortcut.

I don't understand this, do you mind explaining a bit may be with an
example on how you want it to work.
--
Regards,
Rafia Sabih

Hi,

On 2019-05-07 09:30:47 +0200, David Fetter wrote:

It can get a little tedious turning on (or off) all the boolean
options to EXPLAIN, so please find attached a shortcut.

I'm not convinced this is a good idea - it seems likely that we'll add
conflicting options at some point, and then this will just be a pain in
the neck.

Greetings,

Andres Freund

On Tue, May 07, 2019 at 11:13:15AM +0300, Sergei Kornilov wrote:

Hi

I liked this idea.

+			timing_set = true;
+			es->timing = defGetBoolean(opt);
+			summary_set = true;
+			es->timing = defGetBoolean(opt);

second es->timing should be es->summary, right?

You are correct! Sorry about the copy-paste-o.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, May 07, 2019 at 11:03:23AM +0200, Rafia Sabih wrote:

On Tue, 7 May 2019 at 09:30, David Fetter <david@fetter.org> wrote:

Folks,

It can get a little tedious turning on (or off) all the boolean
options to EXPLAIN, so please find attached a shortcut.

I don't understand this, do you mind explaining a bit may be with an
example on how you want it to work.

If you're tuning a query interactively, it's a lot simpler to prepend,
for example,

EXPLAIN (ALL, FORMAT JSON)

to it than to prepend something along the lines of

EXPLAIN(ANALYZE, VERBOSE, COSTS, BUFFERS, SETTINGS, TIMING, SUMMARY, PARTRIDGE_IN_A_PEAR_TREE, FORMAT JSON)

to it.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, May 07, 2019 at 08:41:47AM -0700, Andres Freund wrote:

Hi,

On 2019-05-07 09:30:47 +0200, David Fetter wrote:

It can get a little tedious turning on (or off) all the boolean
options to EXPLAIN, so please find attached a shortcut.

I'm not convinced this is a good idea - it seems likely that we'll
add conflicting options at some point, and then this will just be a
pain in the neck.

I already left out FORMAT for a similar reason, namely that it's not a
boolean, so it's not part of flipping on (or off) all the switches.

Are you seeing a point in the future where there'd be both mutually
exclusive boolean options and no principled reason to choose among
them? If so, what might it look like?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi,

On 2019-05-07 18:34:11 +0200, David Fetter wrote:

On Tue, May 07, 2019 at 08:41:47AM -0700, Andres Freund wrote:

On 2019-05-07 09:30:47 +0200, David Fetter wrote:

It can get a little tedious turning on (or off) all the boolean
options to EXPLAIN, so please find attached a shortcut.

I'm not convinced this is a good idea - it seems likely that we'll
add conflicting options at some point, and then this will just be a
pain in the neck.

I already left out FORMAT for a similar reason, namely that it's not a
boolean, so it's not part of flipping on (or off) all the switches.

Which is already somewhat hard to explain.

Imagine if we had CPU_PROFILE = on (which'd be *extremely*
useful). Would you want that to be switched on automatically? How about
RECORD_IO_TRACE? How about DISTINCT_BUFFERS which'd be like BUFFERS
except that we'd track how many different buffers are accessed using HLL
or such? Would also be extremely useful.

Are you seeing a point in the future where there'd be both mutually
exclusive boolean options and no principled reason to choose among
them? If so, what might it look like?

Yes. CPU_PROFILE_PERF, CPU_PROFILE_VTUNE. And lots more.

Greetings,

Andres Freund

On Tue, May 07, 2019 at 09:44:30AM -0700, Andres Freund wrote:

Hi,

On 2019-05-07 18:34:11 +0200, David Fetter wrote:

On Tue, May 07, 2019 at 08:41:47AM -0700, Andres Freund wrote:

On 2019-05-07 09:30:47 +0200, David Fetter wrote:

It can get a little tedious turning on (or off) all the boolean
options to EXPLAIN, so please find attached a shortcut.

I'm not convinced this is a good idea - it seems likely that we'll
add conflicting options at some point, and then this will just be a
pain in the neck.

I already left out FORMAT for a similar reason, namely that it's not a
boolean, so it's not part of flipping on (or off) all the switches.

Which is already somewhat hard to explain.

Imagine if we had CPU_PROFILE = on (which'd be *extremely*
useful). Would you want that to be switched on automatically? How about
RECORD_IO_TRACE? How about DISTINCT_BUFFERS which'd be like BUFFERS
except that we'd track how many different buffers are accessed using HLL
or such? Would also be extremely useful.

Are you seeing a point in the future where there'd be both mutually
exclusive boolean options and no principled reason to choose among
them? If so, what might it look like?

Yes. CPU_PROFILE_PERF, CPU_PROFILE_VTUNE. And lots more.

Thanks for clarifying.

Would you agree that there's a problem here as I described as
motivation for people who operate databases?

If so, do you have one or more abbreviations in mind that aren't
called ALL? I realize that Naming Things™ is one of two hard problems
in computer science, but it's still one we have to tackle.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, May 7, 2019 at 9:31 AM David Fetter <david@fetter.org> wrote:

If you're tuning a query interactively, it's a lot simpler to prepend,
for example,

EXPLAIN (ALL, FORMAT JSON)

to it than to prepend something along the lines of

EXPLAIN(ANALYZE, VERBOSE, COSTS, BUFFERS, SETTINGS, TIMING, SUMMARY, PARTRIDGE_IN_A_PEAR_TREE, FORMAT JSON)

to it.

FWIW, I have the following in my psqlrc:

\set ea 'EXPLAIN (ANALYZE, SETTINGS, VERBOSE, BUFFERS) '

The idea behind that is that I can prepend ":ea" as needed, rather
than doing a lot of typing each time, as in:

:ea SELECT ...

--
Peter Geoghegan

Hi,

On 2019-05-07 23:23:55 +0200, David Fetter wrote:

Would you agree that there's a problem here as I described as
motivation for people who operate databases?

Yea, but I don't think the solution is where you seek it. I think the
problem is that our defaults for EXPLAIN, in particular EXPLAIN ANALYZE,
are dumb. And that your desire for ALL stems from that, rather than it
being desirable on its own.

We really e.g. should just enable BUFFERS by default. The reason we
can't is that right now we have checks like:
EXPLAIN (BUFFERS) SELECT 1;
ERROR: 22023: EXPLAIN option BUFFERS requires ANALYZE
LOCATION: ExplainQuery, explain.c:206

but we ought to simply remove them. There's no benefit, and besides
preventing from enabling BUFFERS by default it means that
enabling/disabling ANALYZE is more work than necessary.

If so, do you have one or more abbreviations in mind that aren't
called ALL? I realize that Naming Things™ is one of two hard problems
in computer science, but it's still one we have to tackle.

As I said, I don't think ALL is a good idea under any name. Like it
just makes no sense to have ANALYZE, SUMMARY, VERBOSE, BUFFERS,
SETTINGS, FORMAT controlled by one option, unless you call it DWIM. It's
several separate axis (query is executed or not (ANALYZE), verbosity
(SUMMARY, VERBOSE), collecting additional information (BUFFERS, TIMING),
output format).

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:

As I said, I don't think ALL is a good idea under any name. Like it
just makes no sense to have ANALYZE, SUMMARY, VERBOSE, BUFFERS,
SETTINGS, FORMAT controlled by one option, unless you call it DWIM. It's
several separate axis (query is executed or not (ANALYZE), verbosity
(SUMMARY, VERBOSE), collecting additional information (BUFFERS, TIMING),
output format).

FWIW, I find this line of argument fairly convincing. There may well
be a case for rethinking just how EXPLAIN's options behave, but "ALL"
doesn't seem like a good conceptual model.

One idea that comes to mind is that VERBOSE could be redefined as some
sort of package of primitive options, including all of the "additional
information" options, with the ability to turn individual ones off again
if you wanted. So for example (VERBOSE, BUFFERS OFF) would give you
everything except buffer stats. We'd need a separate flag/flags to
control what VERBOSE originally did, but that doesn't bother me ---
it's an opportunity for more clarity of definition, anyway.

I do feel that it's a good idea to keep ANALYZE separate. "Execute
the query or not" is a mighty fundamental thing. I've never liked
that name for the option though --- maybe we could deprecate it
in favor of EXECUTE?

regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Andres Freund <andres@anarazel.de> writes:

As I said, I don't think ALL is a good idea under any name. Like it
just makes no sense to have ANALYZE, SUMMARY, VERBOSE, BUFFERS,
SETTINGS, FORMAT controlled by one option, unless you call it DWIM. It's
several separate axis (query is executed or not (ANALYZE), verbosity
(SUMMARY, VERBOSE), collecting additional information (BUFFERS, TIMING),
output format).

FWIW, I find this line of argument fairly convincing. There may well
be a case for rethinking just how EXPLAIN's options behave, but "ALL"
doesn't seem like a good conceptual model.

One idea that comes to mind is that VERBOSE could be redefined as some
sort of package of primitive options, including all of the "additional
information" options, with the ability to turn individual ones off again
if you wanted. So for example (VERBOSE, BUFFERS OFF) would give you
everything except buffer stats. We'd need a separate flag/flags to
control what VERBOSE originally did, but that doesn't bother me ---
it's an opportunity for more clarity of definition, anyway.

I'm generally in favor of doing something like what Tom is suggesting
with VERBOSE, but I also feel like it should be the default for formats
like JSON. If you're asking for the output in JSON, then we really
should include everything that a flag like VERBOSE would contain because
you're pretty clearly planning to copy/paste that output into something
else to read it anyway.

I do feel that it's a good idea to keep ANALYZE separate. "Execute
the query or not" is a mighty fundamental thing. I've never liked
that name for the option though --- maybe we could deprecate it
in favor of EXECUTE?

Let's not fool ourselves by saying we'd 'deprecate' it because that
implies, at least to me, that there's some intention of later on
removing it and people will potentially propose patches to do that,
which we will then almost certainly spend hours arguing about with the
result being that we don't actually remove it.

I'm all in favor of adding an alias for analyze called 'execute', as
that makes a lot more sense and then updating our documentation to use
it, with 'analyze is accepted as an alias' as a footnote.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

I'm generally in favor of doing something like what Tom is suggesting
with VERBOSE, but I also feel like it should be the default for formats
like JSON. If you're asking for the output in JSON, then we really
should include everything that a flag like VERBOSE would contain because
you're pretty clearly planning to copy/paste that output into something
else to read it anyway.

Meh --- I don't especially care for non-orthogonal behaviors like that.
If you wanted JSON but *not* all of the additional info, how would you
specify that? (The implementation I had in mind would make VERBOSE OFF
more or less a no-op, so that wouldn't get you there.)

I do feel that it's a good idea to keep ANALYZE separate. "Execute
the query or not" is a mighty fundamental thing. I've never liked
that name for the option though --- maybe we could deprecate it
in favor of EXECUTE?

Let's not fool ourselves by saying we'd 'deprecate' it because that
implies, at least to me, that there's some intention of later on
removing it

True, the odds of ever actually removing it are small :-(. I meant
mostly changing all of our docs to use the other spelling, except
for some footnote. Maybe we could call ANALYZE a "legacy spelling"
of EXECUTE.

regards, tom lane

On Tue, May 07, 2019 at 06:12:56PM -0400, Stephen Frost wrote:

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

One idea that comes to mind is that VERBOSE could be redefined as
some sort of package of primitive options, including all of the
"additional information" options, with the ability to turn
individual ones off again if you wanted. So for example (VERBOSE,
BUFFERS OFF) would give you everything except buffer stats. We'd
need a separate flag/flags to control what VERBOSE originally did,
but that doesn't bother me --- it's an opportunity for more
clarity of definition, anyway.

I'm generally in favor of doing something like what Tom is
suggesting with VERBOSE, but I also feel like it should be the
default for formats like JSON. If you're asking for the output in
JSON, then we really should include everything that a flag like
VERBOSE would contain because you're pretty clearly planning to
copy/paste that output into something else to read it anyway.

So basically, every format but text gets the full treatment for
(essentially) the functionality I wrapped up in ALL? That makes a lot
of sense.

I do feel that it's a good idea to keep ANALYZE separate. "Execute
the query or not" is a mighty fundamental thing. I've never liked
that name for the option though --- maybe we could deprecate it
in favor of EXECUTE?

Let's not fool ourselves by saying we'd 'deprecate' it because that
implies, at least to me, that there's some intention of later on
removing it and people will potentially propose patches to do that,
which we will then almost certainly spend hours arguing about with the
result being that we don't actually remove it.

Excellent point.

I'm all in favor of adding an alias for analyze called 'execute', as
that makes a lot more sense and then updating our documentation to
use it, with 'analyze is accepted as an alias' as a footnote.

How about making ANALYZE a backward-compatibility feature in the sense
of replacing examples, docs, etc., with EXECUTE? If most of our users
are in the future, this makes those same users's better without
qualification, and helps some positive fraction of our current users.

On a slightly related topic, we haven't, to date, made any promises
about what EXPLAIN will put out, but as we make more machine-readable
versions, we should at least think about its schema and versioning
of same.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

I'm generally in favor of doing something like what Tom is suggesting
with VERBOSE, but I also feel like it should be the default for formats
like JSON. If you're asking for the output in JSON, then we really
should include everything that a flag like VERBOSE would contain because
you're pretty clearly planning to copy/paste that output into something
else to read it anyway.

Meh --- I don't especially care for non-orthogonal behaviors like that.
If you wanted JSON but *not* all of the additional info, how would you
specify that? (The implementation I had in mind would make VERBOSE OFF
more or less a no-op, so that wouldn't get you there.)

You'd do it the same way you proposed for verbose- eg: BUFFERS OFF, et
al, but, really, the point here is that what you're doing with the JSON
result is fundamentally different- you're going to paste it into some
other tool and it should be that tool's job to manage the visualization
of it and what's included or not in what you see. Passing the
information about what should be seen in the json-based EXPLAIN viewer
by way of omitting things from the JSON output strikes me as downright
odd, and doesn't give that other tool the ability to show that data if
the users ends up wanting it without rerunning the query.

I do feel that it's a good idea to keep ANALYZE separate. "Execute
the query or not" is a mighty fundamental thing. I've never liked
that name for the option though --- maybe we could deprecate it
in favor of EXECUTE?

Let's not fool ourselves by saying we'd 'deprecate' it because that
implies, at least to me, that there's some intention of later on
removing it

True, the odds of ever actually removing it are small :-(. I meant
mostly changing all of our docs to use the other spelling, except
for some footnote. Maybe we could call ANALYZE a "legacy spelling"
of EXECUTE.

Sure, that'd be fine too.

Thanks,

Stephen

Greetings,

* David Fetter (david@fetter.org) wrote:

On Tue, May 07, 2019 at 06:12:56PM -0400, Stephen Frost wrote:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

One idea that comes to mind is that VERBOSE could be redefined as
some sort of package of primitive options, including all of the
"additional information" options, with the ability to turn
individual ones off again if you wanted. So for example (VERBOSE,
BUFFERS OFF) would give you everything except buffer stats. We'd
need a separate flag/flags to control what VERBOSE originally did,
but that doesn't bother me --- it's an opportunity for more
clarity of definition, anyway.

I'm generally in favor of doing something like what Tom is
suggesting with VERBOSE, but I also feel like it should be the
default for formats like JSON. If you're asking for the output in
JSON, then we really should include everything that a flag like
VERBOSE would contain because you're pretty clearly planning to
copy/paste that output into something else to read it anyway.

So basically, every format but text gets the full treatment for
(essentially) the functionality I wrapped up in ALL? That makes a lot
of sense.

Something along those lines is what I was thinking, yes, and it's what
at least some other projects do (admittedly, that's at least partially
my fault because I'm thinking of the 'info' command for pgbackrest, but
David Steele seemed to think it made sense also, at least).

I'm all in favor of adding an alias for analyze called 'execute', as
that makes a lot more sense and then updating our documentation to
use it, with 'analyze is accepted as an alias' as a footnote.

How about making ANALYZE a backward-compatibility feature in the sense
of replacing examples, docs, etc., with EXECUTE? If most of our users
are in the future, this makes those same users's better without
qualification, and helps some positive fraction of our current users.

I'd rather not refer to it as a backwards-compatibility feature since
we, thankfully, don't typically do that and I generally think that's the
right way to go- but in some cases, like this one, having a 'legacy'
spelling or an alias seems to be darn near free without opening the box
of trying to provide backwards compatibility for everything.

On a slightly related topic, we haven't, to date, made any promises
about what EXPLAIN will put out, but as we make more machine-readable
versions, we should at least think about its schema and versioning
of same.

Not really sure that I agree on this point. Do you see a reason to need
versioning or schema when the schema is, essentially, included in each
result since it's JSON or the other machine-readable formats? I can
imagine that we might need a version if we decided to redefine some
existing field in a non-compatible or non-sensible way, but is that
possibility likely enough to warrent adding versioning and complicating
everything downstream? I have a hard time seeing that.

Thanks,

Stephen

On Tue, May 7, 2019 at 6:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Meh --- I don't especially care for non-orthogonal behaviors like that.
If you wanted JSON but *not* all of the additional info, how would you
specify that? (The implementation I had in mind would make VERBOSE OFF
more or less a no-op, so that wouldn't get you there.)

+1. Assuming we know which information the user wants on the basis of
their choice of output format seems like a bad idea. I mean, suppose
we introduced a new option that gathered lots of additional detail but
made the query run 3x slower. Would everyone want that enabled all
the time any time they chose a non-text format? Probably not.

If people want BUFFERS turned on essentially all the time, then let's
just flip the default for that, so that EXPLAIN ANALYZE does the
equivalent of what EXPLAIN (ANALYZE, BUFFERS) currently does, and make
people say EXPLAIN (ANALYZE, BUFFERS OFF) if they don't want all that
detail. I think that's more or less what Andres was suggesting
upthread.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, May 7, 2019 at 12:31 PM David Fetter <david@fetter.org> wrote:

If you're tuning a query interactively, it's a lot simpler to prepend,
for example,

EXPLAIN (ALL, FORMAT JSON)

to it than to prepend something along the lines of

EXPLAIN(ANALYZE, VERBOSE, COSTS, BUFFERS, SETTINGS, TIMING, SUMMARY, PARTRIDGE_IN_A_PEAR_TREE, FORMAT JSON)

to it.

This is something of an exaggeration of what could ever be necessary,
because COSTS and TIMING default to TRUE and SUMMARY defaults to TRUE
when ANALYZE is specified, and the PARTRIDGE_IN_A_PEAR_TREE option
seems not to have made it into the tree this cycle.

But you could need EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS,
FORMAT JSON), which is not quite so long, but admittedly still
somewhat long. Flipping some of the defaults seems like it might be
the way to go. I think turning SETTINGS and BUFFERS on by default
would be pretty sensible.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company