Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-21 Thread Pete Hollobon 
On 21 May 2016 16:07, "David G. Johnston" 
wrote:
> ​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.

A GUC seems like overkill for psql. I have the following in my .psqlrc:

\set expall 'EXPLAIN (analyze, buffers, costs, timing, verbose) '

That lets you type

:expall select a, b from whatever;

For GUI tools like pgadmin you've often got built in explain tools anyway.

> David J.
>

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-21 Thread Tom Lane 
"David G. Johnston"  writes:
> On Sat, May 21, 2016 at 10:32 AM, Euler Taveira 
> 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-21 Thread David G. Johnston 
On Sat, May 21, 2016 at 10:32 AM, Euler Taveira 
wrote:

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

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-21 Thread Euler Taveira 
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, Suporte 24x7 e Treinamento


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-20 Thread Robert Haas 
On Thu, May 19, 2016 at 6:24 PM, Евгений Шишкин  wrote:
>> On 20 May 2016, at 01:12, Tom Lane  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread David Christensen 

> On May 19, 2016, at 5:24 PM, Евгений Шишкин  wrote:
> 
> 
>> On 20 May 2016, at 01:12, Tom Lane  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 future time.

+1.
--
David Christensen
End Point Corporation
da...@endpoint.com
785-727-1171





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Евгений Шишкин 

> On 20 May 2016, at 01:12, Tom Lane  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.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Gavin Flower 

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???  :-)


[...]


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Tom Lane 
=?utf-8?B?0JXQstCz0LXQvdC40Lkg0KjQuNGI0LrQuNC9?=  writes:
>> On 19 May 2016, at 22:59, Tom Lane  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread David G. Johnston 
On Thursday, May 19, 2016, David Christensen  wrote:

>
> > On May 19, 2016, at 3:17 PM, Евгений Шишкин  > wrote:
> >
> >
> >> On 19 May 2016, at 22:59, Tom Lane >
> wrote:
> >>
> >> David Christensen > 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.

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:

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.

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread David Christensen 

> On May 19, 2016, at 3:17 PM, Евгений Шишкин  wrote:
> 
> 
>> On 19 May 2016, at 22:59, Tom Lane  wrote:
>> 
>> David Christensen  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
da...@endpoint.com
785-727-1171





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Alvaro Herrera 
Евгений Шишкин 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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Евгений Шишкин 

> On 19 May 2016, at 22:59, Tom Lane  wrote:
> 
> David Christensen  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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Peter Geoghegan 
On Thu, May 19, 2016 at 12:59 PM, Tom Lane  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add EXPLAIN (ALL) shorthand

2016-05-19 Thread Tom Lane 
David Christensen  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers