New EXPLAIN option: ALL

classic Classic list List threaded Threaded
35 messages Options
12
Reply | Threaded
Open this post in threaded view
|

New EXPLAIN option: ALL

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

v1-0001-Add-an-ALL-option-to-EXPLAIN.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

David Fetter
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

v2-0001-Add-an-ALL-option-to-EXPLAIN.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Sergei Kornilov
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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Rafia Sabih
In reply to this post by David Fetter
On Tue, 7 May 2019 at 09:30, David Fetter <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Andres Freund
In reply to this post by David Fetter
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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

David Fetter
In reply to this post by Sergei Kornilov
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

v2-0001-Add-an-ALL-option-to-EXPLAIN.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

David Fetter
In reply to this post by Rafia Sabih
On Tue, May 07, 2019 at 11:03:23AM +0200, Rafia Sabih wrote:
> On Tue, 7 May 2019 at 09:30, David Fetter <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

David Fetter
In reply to this post by Andres Freund
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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Andres Freund
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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

David Fetter
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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Peter Geoghegan-4
In reply to this post by David Fetter
On Tue, May 7, 2019 at 9:31 AM David Fetter <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Andres Freund
In reply to this post by David Fetter
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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Tom Lane-2
Andres Freund <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Stephen Frost
Greetings,

* Tom Lane ([hidden email]) wrote:

> Andres Freund <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Tom Lane-2
Stephen Frost <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

David Fetter
In reply to this post by Stephen Frost
On Tue, May 07, 2019 at 06:12:56PM -0400, Stephen Frost wrote:

> Greetings,
>
> * Tom Lane ([hidden email]) 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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Stephen Frost
In reply to this post by Tom Lane-2
Greetings,

* Tom Lane ([hidden email]) wrote:

> Stephen Frost <[hidden email]> 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Stephen Frost
In reply to this post by David Fetter
Greetings,

* David Fetter ([hidden email]) wrote:

> On Tue, May 07, 2019 at 06:12:56PM -0400, Stephen Frost wrote:
> > * Tom Lane ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Robert Haas
In reply to this post by Tom Lane-2
On Tue, May 7, 2019 at 6:25 PM Tom Lane <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: New EXPLAIN option: ALL

Robert Haas
In reply to this post by David Fetter
On Tue, May 7, 2019 at 12:31 PM David Fetter <[hidden email]> 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


12