Обсуждение: [PATCH] Add EXPLAIN (ALL) shorthand
This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)” forusability. -- David Christensen End Point Corporation david@endpoint.com 785-727-1171
Вложения
David Christensen <david@endpoint.com> writes: > This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)”for usability. I'm not sure this is well thought out. It would mean for example that we could never implement EXPLAIN options that are mutually exclusive ... at least, not without having to redefine ALL as all-except-something. Non-boolean options would be problematic as well. regards, tom lane
On Thu, May 19, 2016 at 12:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I'm not sure this is well thought out. It would mean for example that > we could never implement EXPLAIN options that are mutually exclusive > ... at least, not without having to redefine ALL as all-except-something. > Non-boolean options would be problematic as well. While the spelling needs work, I like the general idea. -- Peter Geoghegan
> On 19 May 2016, at 22:59, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > David Christensen <david@endpoint.com> writes: >> This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)”for usability. > > I'm not sure this is well thought out. It would mean for example that > we could never implement EXPLAIN options that are mutually exclusive > ... at least, not without having to redefine ALL as all-except-something. > Non-boolean options would be problematic as well. > Maybe EVERYTHING would be ok. But it is kinda long word to type. > regards, tom lane > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
Евгений Шишкин wrote: > Maybe EVERYTHING would be ok. > But it is kinda long word to type. There's never need to run the EXPLAIN (EVERTHING) command; you already know that the answer is 42. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> On May 19, 2016, at 3:17 PM, Евгений Шишкин <itparanoia@gmail.com> wrote: > > >> On 19 May 2016, at 22:59, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> David Christensen <david@endpoint.com> writes: >>> This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)”for usability. >> >> I'm not sure this is well thought out. It would mean for example that >> we could never implement EXPLAIN options that are mutually exclusive >> ... at least, not without having to redefine ALL as all-except-something. >> Non-boolean options would be problematic as well. >> > > Maybe EVERYTHING would be ok. > But it is kinda long word to type. If it’s just a terminology issue, what about EXPLAIN (*); already a precedent with SELECT * to mean “everything”. (MAX?LIKE_I’M_5?) Let the bikeshedding begin! In any case, I think a shorthand for “give me the most possible detail without me having to lookup/type/remember the options”is a good tool. David -- David Christensen End Point Corporation david@endpoint.com 785-727-1171
On Thursday, May 19, 2016, David Christensen <david@endpoint.com> wrote:
The bigger question is do we want to solve this in the server or let it be a client concern and stick some usability enhancements into psql? Maybe even put it in psql first then migrate to the server after some field experience.
The middle road is probably something like the following:
> On May 19, 2016, at 3:17 PM, Евгений Шишкин <itparanoia@gmail.com> wrote:
>
>
>> On 19 May 2016, at 22:59, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> David Christensen <david@endpoint.com> writes:
>>> This simple patch adds “ALL” as an EXPLAIN option as shorthand for “EXPLAIN (ANALYZE, VERBOSE, COSTS, TIMING, BUFFERS)” for usability.
>>
>> I'm not sure this is well thought out. It would mean for example that
>> we could never implement EXPLAIN options that are mutually exclusive
>> ... at least, not without having to redefine ALL as all-except-something.
>> Non-boolean options would be problematic as well.
>>
>
> Maybe EVERYTHING would be ok.
> But it is kinda long word to type.
If it’s just a terminology issue, what about EXPLAIN (*); already a precedent with SELECT * to mean “everything”. (MAX?
LIKE_I’M_5?) Let the bikeshedding begin!
+1
In any case, I think a shorthand for “give me the most possible detail without me having to lookup/type/remember the options” is a good tool.
EXPLAIN THIS
EXPLAIN BETTER
EXPAIN ABCTV (might need permission to document this variation though)
The later has some resemblance to option short form in command lines.
We could setup a guc for "default_explain_options" that the user could set or have set for them using alter role. Maybe we'd want to include a new option "CLEAN" or "NONE" that tells the system to skip those default and only use ones that are explicitly specified in the SQL command. Basically an envvar like many command line apps use in lieu of an .rc file but with a simpler way to disable than setting it to nothing.
David J.
Евгений Шишкин <itparanoia@gmail.com> writes: >> On 19 May 2016, at 22:59, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I'm not sure this is well thought out. It would mean for example that >> we could never implement EXPLAIN options that are mutually exclusive >> ... at least, not without having to redefine ALL as all-except-something. >> Non-boolean options would be problematic as well. > Maybe EVERYTHING would be ok. That's not really getting at the substance of my complaint, which is that I foresee regretting inventing such an option later, when we wish to invent some option that it's not reasonable for ALL/EVERYTHING/WHATEVER to turn "on" (if indeed the new option is "off"/"on" in the first place). We'll either have to accept an arbitrary/inconsistent definition of what ALL does to that option, or have to explain that ALL doesn't mean all. My own experience is that I seldom want ANALYZE and VERBOSE at the same time, so I'm doubtful that I'd find this proposal helpful even just considering the current option set. I'm also not convinced about the semantics of, say, EXPLAIN (ALL, COSTS OFF). I see what the patch will do, but I'm not convinced I like that (and I am convinced that the patch's documentation is misleading about it). I'm a bit inclined to think that what this is really about is that we made the wrong call on the BUFFERS option, and that it should default to ON just like COSTS and TIMING do. Yeah, that would be an incompatible change, but that's what major releases are for no? regards, tom lane
On 20/05/16 10:11, David G. Johnston wrote: [...] > > EXPAIN ABCTV (might need permission to document this variation though) What has an Australian Broadcast Corporation Television got to do with this??? :-) [...]
> On 20 May 2016, at 01:12, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > I'm a bit inclined to think that what this is really about is that we > made the wrong call on the BUFFERS option, and that it should default > to ON just like COSTS and TIMING do. Yeah, that would be an incompatible > change, but that's what major releases are for no? After thinking about it, i think this is a better idea.
> On May 19, 2016, at 5:24 PM, Евгений Шишкин <itparanoia@gmail.com> wrote: > > >> On 20 May 2016, at 01:12, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> >> I'm a bit inclined to think that what this is really about is that we >> made the wrong call on the BUFFERS option, and that it should default >> to ON just like COSTS and TIMING do. Yeah, that would be an incompatible >> change, but that's what major releases are for no? > > After thinking about it, i think this is a better idea. Yeah, if that’s the only practical difference, WORKSFORME; I can see the point about boxing us into a corner at some futuretime. +1. -- David Christensen End Point Corporation david@endpoint.com 785-727-1171
On Thu, May 19, 2016 at 6:24 PM, Евгений Шишкин <itparanoia@gmail.com> wrote: >> On 20 May 2016, at 01:12, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I'm a bit inclined to think that what this is really about is that we >> made the wrong call on the BUFFERS option, and that it should default >> to ON just like COSTS and TIMING do. Yeah, that would be an incompatible >> change, but that's what major releases are for no? > > After thinking about it, i think this is a better idea. Hmm, my experience is different. I use EXPLAIN (ANALYZE, VERBOSE) a lot, but EXPLAIN (ANALYZE, BUFFERS) only rarely. I wonder if a GUC is the way to go. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 20-05-2016 20:34, Robert Haas wrote: > Hmm, my experience is different. I use EXPLAIN (ANALYZE, VERBOSE) a > lot, but EXPLAIN (ANALYZE, BUFFERS) only rarely. I wonder if a GUC is > the way to go. > I wouldn't like a command output controlled by GUC. EXPLAIN is used a lot in bug/performance reports. I'm predicting users that will have trouble while using EXPLAIN if someone change the suggested GUC. It also breaks clients/applications that parse EXPLAIN. I don't want another bytea_output. However, if someone wants to suggest turning on/off some option defaults, I'm all ears. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte24x7 e Treinamento
On 20-05-2016 20:34, Robert Haas wrote:
> Hmm, my experience is different. I use EXPLAIN (ANALYZE, VERBOSE) a
> lot, but EXPLAIN (ANALYZE, BUFFERS) only rarely. I wonder if a GUC is
> the way to go.
>
I wouldn't like a command output controlled by GUC. EXPLAIN is used a
lot in bug/performance reports.
And most of the time the choice of options is totally arbitrary based upon the mood and experience of the user, so what's it matter if they saved a few keystrokes and set the GUC in the .psqlrc file?
I'm predicting users that will have
trouble while using EXPLAIN if someone change the suggested GUC. It also
breaks clients/applications that parse EXPLAIN.
Pretty much the same argument as above.
I would not expect a DBA to set this value globally - but shame on them if they do. I'd expect either ALTER ROLE or SET usage, in .psqlrc if applicable, to be the dominate usage for setting the value to a non-empty string. There is UI to consider here but I don't see any fundamental problems.
I don't want another
bytea_output. However, if someone wants to suggest turning on/off some
option defaults, I'm all ears.
I don't see the resemblance. There was, and is, no way to change the output format of bytea within the SELECT SQL statement, only the via GUC. But with explain everything you could do with the GUC can and is already being done by people simply tossing or excluding options on the EXPLAIN SQL statement they write.
All this does is allow the user to setup their preferred defaults in said GUC so they don't have to keep typing them in over and over again. If the GUC results in a broken output in whatever context you care about the user doing it manually would have the same result.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Sat, May 21, 2016 at 10:32 AM, Euler Taveira <euler@timbira.com.br> > wrote: >> I wouldn't like a command output controlled by GUC. EXPLAIN is used a >> lot in bug/performance reports. > And most of the time the choice of options is totally arbitrary based upon > the mood and experience of the user, so what's it matter if they saved a > few keystrokes and set the GUC in the .psqlrc file? We've learned repeatedly that GUCs that affect command semantics are dangerous. I do not think there's enough justification for that here. regards, tom lane
<p dir="ltr"><br /> On 21 May 2016 16:07, "David G. Johnston" <<a href="mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.com</a>>wrote:<br /> > And most of the time the choiceof options is totally arbitrary based upon the mood and experience of the user, so what's it matter if they saved afew keystrokes and set the GUC in the .psqlrc file?<br /> ><br /> >> I'm predicting users that will have<br />>> trouble while using EXPLAIN if someone change the suggested GUC. It also<br /> >> breaks clients/applicationsthat parse EXPLAIN.<br /> ><br /> ><br /> > Pretty much the same argument as above.<br />><br /> > I would not expect a DBA to set this value globally - but shame on them if they do. I'd expect eitherALTER ROLE or SET usage, in .psqlrc if applicable, to be the dominate usage for setting the value to a non-empty string. There is UI to consider here but I don't see any fundamental problems.<p dir="ltr">A GUC seems like overkill forpsql. I have the following in my .psqlrc: <p dir="ltr">\set expall 'EXPLAIN (analyze, buffers, costs, timing, verbose)'<p dir="ltr">That lets you type<p dir="ltr">:expall select a, b from whatever;<p dir="ltr">For GUI tools like pgadminyou've often got built in explain tools anyway.<p dir="ltr">> David J.<br /> >