Now that Bruce has done some work on rationalizing elog() output, seems
like we ought to take another look at EXPLAIN VERBOSE.  Currently, that
command does this:

1. A non-pretty-printed plan dump (nodeToString output) is sent to
elog(INFO).  Formerly that always output to both postmaster log and
client, but now it'll typically go only to the client.

2. The short-form output (same as non-VERBOSE EXPLAIN) is sent to
elog(INFO).  See above.

3. The pretty-printed plan dump is sent to postmaster stdout.

Now postmaster stdout is just about the least good destination we
could possibly use.  It may well end up in the bit bucket (if someone is
only saving stderr output, and/or is using syslog logging instead of
stderr).  In any case it's not necessarily an easy place for the client
to get at.

Also, I find the non-pretty-printed dump format damn near unreadable,
although I have seen comments suggesting that there are some people who
actually like it.  I don't see the point of giving it pride of place on
the client's terminal.

What I would suggest is that EXPLAIN VERBOSE ought to emit either
non-pretty-print or pretty-print dump format, not both (probably control
this with debug_pretty_print or another newly-invented GUC parameter;
IMHO the factory default should be pretty-printing).  Furthermore, the
output should go to elog(INFO) in either case.  This will take some work
to make the prettyprinter capable of that, but it's not a big job.
(A side effect of this is that pprint dumps logged by the
debug_print_plan and so forth options could go through elog as well,
which they don't now.)

A disadvantage of elog(INFO) output for pretty-printed plans is that
AFAIK psql doesn't currently have any way of capturing NOTICE output
into a file.  I find it much better to look at pretty-printed dumps
in Emacs than on a terminal window, mainly because Emac's M-C-f and
M-C-b commands understand the nesting structure so it's easy to move
around in the dump with them.  How hard would it be to get psql to
send notice output into a \g file?

Comments?  In particular, should EXPLAIN use the existing
debug_pretty_print GUC variable, or have its own?

			regards, tom lane

Sat, 09 Mar 2002 14:09:21 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Rationalizing EXPLAIN VERBOSE output
Tom Lane wrote:
> Now that Bruce has done some work on rationalizing elog() output, seems
> like we ought to take another look at EXPLAIN VERBOSE.  Currently, that
> command does this:

Yes, the elog() tags finally match some reality.  :-)

> 1. A non-pretty-printed plan dump (nodeToString output) is sent to
> elog(INFO).  Formerly that always output to both postmaster log and
> client, but now it'll typically go only to the client.
> 
> 2. The short-form output (same as non-VERBOSE EXPLAIN) is sent to
> elog(INFO).  See above.
> 
> 3. The pretty-printed plan dump is sent to postmaster stdout.
> 
> Now postmaster stdout is just about the least good destination we
> could possibly use.  It may well end up in the bit bucket (if someone is
> only saving stderr output, and/or is using syslog logging instead of
> stderr).  In any case it's not necessarily an easy place for the client
> to get at.


Seems EXPLAIN may need a level capability like DEBUG1-5 now.  We have
EXPLAIN and EXPLAIN VERBOSE.  Now have pretty print vs. "jumble" print,
which some people do actually prefer.  They must have better cognitive
skills than me.

We now also have the index clause printing that you mentioned.  Should
we go with some kind of numeric level to EXPLAIN that would control
this?

That is the only simple solution I can think of.  GUC seems way beyond
what someone would want.  Having SET control EXPLAIN just seems overkill
because EXPLAIN should be able to control itself.

Also, clearly, we need to fix the output of pretty print to honor ELOG
control, and in any other places we may have missed it.

How about?

	EXPLAIN select * from pg_class;
	EXPLAIN VERBOSE select * from pg_class;
	EXPLAIN VERBOSE 1 select * from pg_class;
	EXPLAIN VERBOSE 5 select * from pg_class;

Seems pretty clear.  VERBOSE takes an optional argument that controls
the level of detail.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 9 Mar 2002 18:49:20 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> How about?

> 	EXPLAIN select * from pg_class;
> 	EXPLAIN VERBOSE select * from pg_class;
> 	EXPLAIN VERBOSE 1 select * from pg_class;
> 	EXPLAIN VERBOSE 5 select * from pg_class;

Seems kinda ugly.  But maybe same idea with repeated VERBOSE,
a la some Unix commands ("more -v's get you more detail"):

	EXPLAIN [ANALYZE] [VERBOSE] [ VERBOSE ... ] statement;

I'd sugggest

EXPLAIN select * from pg_class;

	Default output: same as now

EXPLAIN VERBOSE select * from pg_class;

	Add prettyprinted qual clauses

EXPLAIN VERBOSE VERBOSE select * from pg_class;

	Add full plan-tree dump

and there's room for expansion if we need it.

There's still the question of how to format the plan-tree dump.
I still rather like a GUC variable for that choice, since it seems
to be a personal preference that's unlikely to change from one
command to the next.

			regards, tom lane

Sun, 10 Mar 2002 11:52:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
Tom Lane writes:

> What I would suggest is that EXPLAIN VERBOSE ought to emit either
> non-pretty-print or pretty-print dump format, not both (probably control
> this with debug_pretty_print or another newly-invented GUC parameter;
> IMHO the factory default should be pretty-printing).

Sounds good.  I think we can reuse the parameter.

> A disadvantage of elog(INFO) output for pretty-printed plans is that
> AFAIK psql doesn't currently have any way of capturing NOTICE output
> into a file.  I find it much better to look at pretty-printed dumps
> in Emacs than on a terminal window, mainly because Emac's M-C-f and
> M-C-b commands understand the nesting structure so it's easy to move
> around in the dump with them.  How hard would it be to get psql to
> send notice output into a \g file?

\g (and \o) send only the query results to a file.  The idea is that you
want to save the results, but if there's a warning or error, you want to
see it.  We could add alternative commands (\G and \O?) that save the
notices and errors as well.  Not sure if this is useful beyond this
application.  In non-interactive situations you'd usually use shell
redirections to save all output.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 10 Mar 2002 21:25:44 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Rationalizing EXPLAIN VERBOSE output
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> ... How hard would it be to get psql to
>> send notice output into a \g file?

> \g (and \o) send only the query results to a file.  The idea is that you
> want to save the results, but if there's a warning or error, you want to
> see it.  We could add alternative commands (\G and \O?) that save the
> notices and errors as well.  Not sure if this is useful beyond this
> application.  In non-interactive situations you'd usually use shell
> redirections to save all output.

The other possibility is to make EXPLAIN output look like a SELECT
result.  Not sure how hard this would be to do, but in the long run
I suppose that would be the direction to move in.

			regards, tom lane

Sun, 10 Mar 2002 21:28:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > What I would suggest is that EXPLAIN VERBOSE ought to emit either
> > non-pretty-print or pretty-print dump format, not both (probably control
> > this with debug_pretty_print or another newly-invented GUC parameter;
> > IMHO the factory default should be pretty-printing).
> 
> Sounds good.  I think we can reuse the parameter.

Agreed.  I like parameter reuse.

> > A disadvantage of elog(INFO) output for pretty-printed plans is that
> > AFAIK psql doesn't currently have any way of capturing NOTICE output
> > into a file.  I find it much better to look at pretty-printed dumps
> > in Emacs than on a terminal window, mainly because Emac's M-C-f and
> > M-C-b commands understand the nesting structure so it's easy to move
> > around in the dump with them.  How hard would it be to get psql to
> > send notice output into a \g file?
> 
> \g (and \o) send only the query results to a file.  The idea is that you
> want to save the results, but if there's a warning or error, you want to
> see it.  We could add alternative commands (\G and \O?) that save the
> notices and errors as well.  Not sure if this is useful beyond this
> application.  In non-interactive situations you'd usually use shell
> redirections to save all output.

Could we send notices to the \g, \o file and to the terminal, and send
normal output only to the file?  Seems that would make sense.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Mar 2002 21:32:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Tom Lane writes:
> >> ... How hard would it be to get psql to
> >> send notice output into a \g file?
> 
> > \g (and \o) send only the query results to a file.  The idea is that you
> > want to save the results, but if there's a warning or error, you want to
> > see it.  We could add alternative commands (\G and \O?) that save the
> > notices and errors as well.  Not sure if this is useful beyond this
> > application.  In non-interactive situations you'd usually use shell
> > redirections to save all output.
> 
> The other possibility is to make EXPLAIN output look like a SELECT
> result.  Not sure how hard this would be to do, but in the long run
> I suppose that would be the direction to move in.

Seems EXPLAIN as SELECT would break our elog() control of output to the
server logs.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Mar 2002 21:33:37 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Seems EXPLAIN as SELECT would break our elog() control of output to the
> server logs.

EXPLAIN as SELECT would mean that the server log is out of the picture
entirely, which is not necessarily a bad thing.  Is there a good reason
for logging EXPLAIN output?  I can't see one other than "we've always
done it that way".

			regards, tom lane

Sun, 10 Mar 2002 21:36:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Seems EXPLAIN as SELECT would break our elog() control of output to the
> > server logs.
> 
> EXPLAIN as SELECT would mean that the server log is out of the picture
> entirely, which is not necessarily a bad thing.  Is there a good reason
> for logging EXPLAIN output?  I can't see one other than "we've always
> done it that way".

I can't think of a good reason, but making it a select output makes
EXPLAIN one of the few things you can't get into the server logs, even
if you want to.  At DEBUG5, you get almost everything about a query. 
Seems you may want to capture EXPLAIN in there too, but because we can
control those with print_* using various SET parameters, I guess it is
OK.

There are other INFO types that are sent to the client that can't be
captured in psql output, like VACUUM VERBOSE.  I guess I would rather
see NOTICES go to the \g/\o output file and to the terminal as a fix
that would solve the problem easily.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Mar 2002 21:48:42 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can't think of a good reason, but making it a select output makes
> EXPLAIN one of the few things you can't get into the server logs, even
> if you want to.  At DEBUG5, you get almost everything about a query. 

... including the query plan dump, no?  I don't see the point here.

One reason in favor of SELECT-like output is that a lot of user
interfaces are not prepared for large NOTICE outputs.  (Even psql
isn't really, since it can't paginate NOTICE output.)

			regards, tom lane

Sun, 10 Mar 2002 22:45:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
Tom Lane writes:

> The other possibility is to make EXPLAIN output look like a SELECT
> result.  Not sure how hard this would be to do, but in the long run
> I suppose that would be the direction to move in.

You could internally rewrite it to something like

select explain('select * from pg_class;');

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 10 Mar 2002 23:25:40 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Rationalizing EXPLAIN VERBOSE output 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> The other possibility is to make EXPLAIN output look like a SELECT
>> result.  Not sure how hard this would be to do, but in the long run
>> I suppose that would be the direction to move in.

> You could internally rewrite it to something like
> select explain('select * from pg_class;');

Having looked, I think it wouldn't be that bad to call the regular
printtup.c routines directly.  Assuming that the output model we
want is "one text column, with one row per line", it'd only be
necessary to fake up a correct TupleDesc and then form a HeapTuple
from each line of output.  Lots less work than trying to rewrite
the query, I think.

			regards, tom lane

Sun, 10 Mar 2002 23:26:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I can't think of a good reason, but making it a select output makes
> > EXPLAIN one of the few things you can't get into the server logs, even
> > if you want to.  At DEBUG5, you get almost everything about a query. 
> 
> ... including the query plan dump, no?  I don't see the point here.
> 
> One reason in favor of SELECT-like output is that a lot of user
> interfaces are not prepared for large NOTICE outputs.  (Even psql
> isn't really, since it can't paginate NOTICE output.)

Pagination is a good point.  EXPLAIN is one of the few cases where the
output is clearly multi-line.  I am concerned that making explain like
SELECT means it is on the one piece of debug info you can't get into the
server logs.  Query dump can already get into the query logs, but not
EXPLAIN non-verbose.

In fact, as Peter explains it, NOTICE \g goes to the terminal because it
is assumed to be an error.  Maybe we need to make psql smarter and only
send ERROR/WARNING to terminal, and INFO/NOTICE to the log file.  With
new elog() levels, seems this is needed anyway.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Mar 2002 23:36:05 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> ... I am concerned that making explain like
> SELECT means it is on the one piece of debug info you can't get into the
> server logs.  Query dump can already get into the query logs, but not
> EXPLAIN non-verbose.

A week ago you were willing to set things up so that INFO output could
not get into the server logs period.  Why so concerned now?  EXPLAIN
output does not seem like suitable data for logs to me, any more than
the output of SELECT queries does.  It's only a historical artifact
that we are accustomed to thinking of it as being loggable.

> In fact, as Peter explains it, NOTICE \g goes to the terminal because it
> is assumed to be an error.  Maybe we need to make psql smarter and only
> send ERROR/WARNING to terminal, and INFO/NOTICE to the log file.

While I suggested that to start with, it seems like a bad idea on
further thought.  Mixing INFO/NOTICE with query output would be just
like piping stdout and stderr to the same place.  There's usually
good reason to keep them separate.

			regards, tom lane

Sun, 10 Mar 2002 23:43:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > How about?
> 
> > 	EXPLAIN select * from pg_class;
> > 	EXPLAIN VERBOSE select * from pg_class;
> > 	EXPLAIN VERBOSE 1 select * from pg_class;
> > 	EXPLAIN VERBOSE 5 select * from pg_class;
> 
> Seems kinda ugly.  But maybe same idea with repeated VERBOSE,
> a la some Unix commands ("more -v's get you more detail"):
> 
> 	EXPLAIN [ANALYZE] [VERBOSE] [ VERBOSE ... ] statement;
> 
> I'd sugggest
> 
> EXPLAIN select * from pg_class;
> 
> 	Default output: same as now
> 
> EXPLAIN VERBOSE select * from pg_class;
> 
> 	Add prettyprinted qual clauses
> 
> EXPLAIN VERBOSE VERBOSE select * from pg_class;
> 
> 	Add full plan-tree dump
> 
> and there's room for expansion if we need it.

I was never a fan of the -v -v more-verbose options, and I don't see any
case where we use such behavior in our code.  We do use detail levels
for debug, and that is fairly common.

How about:

> > 	EXPLAIN select * from pg_class;
> > 	EXPLAIN VERBOSE select * from pg_class;
> > 	EXPLAIN LEVEL 1 select * from pg_class;
> > 	EXPLAIN LEVEL 5 select * from pg_class;

Here I use LEVEL to tell how much detail you want.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Mar 2002 23:45:18 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Bruce Momjian writes:

> In fact, as Peter explains it, NOTICE \g goes to the terminal because it
> is assumed to be an error.  Maybe we need to make psql smarter and only
> send ERROR/WARNING to terminal, and INFO/NOTICE to the log file.  With
> new elog() levels, seems this is needed anyway.

INFO is just as irrelevant to the query results as WARNING is.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 10 Mar 2002 23:47:15 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Rationalizing EXPLAIN VERBOSE output
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > ... I am concerned that making explain like
> > SELECT means it is on the one piece of debug info you can't get into the
> > server logs.  Query dump can already get into the query logs, but not
> > EXPLAIN non-verbose.
> 
> A week ago you were willing to set things up so that INFO output could
> not get into the server logs period.  Why so concerned now?  EXPLAIN
> output does not seem like suitable data for logs to me, any more than
> the output of SELECT queries does.  It's only a historical artifact
> that we are accustomed to thinking of it as being loggable.
> 
> > In fact, as Peter explains it, NOTICE \g goes to the terminal because it
> > is assumed to be an error.  Maybe we need to make psql smarter and only
> > send ERROR/WARNING to terminal, and INFO/NOTICE to the log file.
> 
> While I suggested that to start with, it seems like a bad idea on
> further thought.  Mixing INFO/NOTICE with query output would be just
> like piping stdout and stderr to the same place.  There's usually
> good reason to keep them separate.

OK, sounds interesting.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Mar 2002 23:48:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
* Tom Lane (tgl@sss.pgh.pa.us) [020310 22:46]:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I can't think of a good reason, but making it a select output makes
> > EXPLAIN one of the few things you can't get into the server logs, even
> > if you want to.  At DEBUG5, you get almost everything about a query. 
> 
> ... including the query plan dump, no?  I don't see the point here.
> 
> One reason in favor of SELECT-like output is that a lot of user
> interfaces are not prepared for large NOTICE outputs.  (Even psql
> isn't really, since it can't paginate NOTICE output.)

Another reason is that explain output would be easily available in
non-postgres specific client utilities written on top of standardized
database interfaces, like ODBC and JDBC.

We're just polishing off a sizable MS SQL Server to PG migration, and
we have a department of three folks that use an ODBC based tool to do
lots of one-off SQL queries.  They like their existing tool, and it
works well.  Getting explain output requires that they either use
PgAdmin II, which they're not used to, or a shell connection to psql,
which they're really not used to, or having the DBA pull the explain
data out of the log, which is truly a nuisance.

So, please, please, please add a select-like output path for explain.
I'm ambivalent about whether or not it still logs the output.

-Brad

Sun, 10 Mar 2002 23:48:58 -0500
Bradley McLean <brad@bradm.net>
Re: Rationalizing EXPLAIN VERBOSE output
Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > In fact, as Peter explains it, NOTICE \g goes to the terminal because it
> > is assumed to be an error.  Maybe we need to make psql smarter and only
> > send ERROR/WARNING to terminal, and INFO/NOTICE to the log file.  With
> > new elog() levels, seems this is needed anyway.
> 
> INFO is just as irrelevant to the query results as WARNING is.

Oh, \g is just the query result, not the query itself.  I get it now.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 10 Mar 2002 23:49:46 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
...
> > >     EXPLAIN VERBOSE select * from pg_class;
> > >     EXPLAIN LEVEL 1 select * from pg_class;
> > >     EXPLAIN LEVEL 5 select * from pg_class;

How about leaving off "LEVEL" and just allow a numeric argument after
VERBOSE? It does not give shift/reduce troubles. And I'm not sure that
"level" makes it clearer (level of what?). So it would be

  EXPLAIN VERBOSE select ...
  EXPLAIN VERBOSE 5 select ...

etc

                        - Thomas

Sun, 10 Mar 2002 21:18:01 -0800
Thomas Lockhart <thomas@fourpalms.org>
Re: Rationalizing EXPLAIN VERBOSE output
Thomas Lockhart wrote:
> ...
> > > >     EXPLAIN VERBOSE select * from pg_class;
> > > >     EXPLAIN LEVEL 1 select * from pg_class;
> > > >     EXPLAIN LEVEL 5 select * from pg_class;
> 
> How about leaving off "LEVEL" and just allow a numeric argument after
> VERBOSE? It does not give shift/reduce troubles. And I'm not sure that
> "level" makes it clearer (level of what?). So it would be
> 
>   EXPLAIN VERBOSE select ...
>   EXPLAIN VERBOSE 5 select ...

Yes, this was my initial proposal but Tom didn't like it.  Seemed very
clear to me.  Tom wants EXPLAIN VERBOSE VERBOSE.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 11 Mar 2002 00:19:04 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Gavin Sherry wrote:
> On Sun, 10 Mar 2002, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> 
> > > Seems kinda ugly.  But maybe same idea with repeated VERBOSE,
> > > a la some Unix commands ("more -v's get you more detail"):
> > > 
> > > 	EXPLAIN [ANALYZE] [VERBOSE] [ VERBOSE ... ] statement;
> > > 
> 
> > 
> > I was never a fan of the -v -v more-verbose options, and I don't see any
> > case where we use such behavior in our code.  We do use detail levels
> > for debug, and that is fairly common.
> 
> I agree. This is fine under Unix, but command arguments are not really a
> grammar. Yacc doesn't enjoy terminal repetition and for good reason: it
> usually suggests a clumsy grammar. 
> 
> Personally, I think that Tom's code should go into standard EXPLAIN.

I am confused.  Which grammar do you like?

> As for how to returning explain data as a SELECT. I think I prefer
> Oracle's idea of output tables with a Postgres twist. EXPLAIN could then
> be something like:
> 
> EXPLAIN [VERBOSE] [SET ID='...' ] [INTO [TEMP] <table>] <query>
> 
> If 'table' exists, EXPLAIN would check if it is a valid explain output
> table (correct attr names, types) and if so insert the results of explain,
> one tuple per line of output. ID would be a text identifier of the output.
> 
> If the table didn't exist, it would be created. TEMP means that the table
> is removed at the end of the session.
> 
> Is this overkill?

That was my initial reaction.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 11 Mar 2002 00:21:29 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
On Sun, 10 Mar 2002, Bruce Momjian wrote:

> Tom Lane wrote:

> > Seems kinda ugly.  But maybe same idea with repeated VERBOSE,
> > a la some Unix commands ("more -v's get you more detail"):
> > 
> > 	EXPLAIN [ANALYZE] [VERBOSE] [ VERBOSE ... ] statement;
> > 

> 
> I was never a fan of the -v -v more-verbose options, and I don't see any
> case where we use such behavior in our code.  We do use detail levels
> for debug, and that is fairly common.

I agree. This is fine under Unix, but command arguments are not really a
grammar. Yacc doesn't enjoy terminal repetition and for good reason: it
usually suggests a clumsy grammar. 

Personally, I think that Tom's code should go into standard EXPLAIN.

As for how to returning explain data as a SELECT. I think I prefer
Oracle's idea of output tables with a Postgres twist. EXPLAIN could then
be something like:

EXPLAIN [VERBOSE] [SET ID='...' ] [INTO [TEMP] <table>] <query>

If 'table' exists, EXPLAIN would check if it is a valid explain output
table (correct attr names, types) and if so insert the results of explain,
one tuple per line of output. ID would be a text identifier of the output.

If the table didn't exist, it would be created. TEMP means that the table
is removed at the end of the session.

Is this overkill?

Gavin



Mon, 11 Mar 2002 16:22:50 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Rationalizing EXPLAIN VERBOSE output
...
> Yes, this was my initial proposal but Tom didn't like it.  Seemed very
> clear to me.  Tom wants EXPLAIN VERBOSE VERBOSE.

Eh. Don't like that myself. How about adding V's to verbose? So

  EXPLAIN VERBOSE
  EXPLAIN VVERBOSE
  EXPLAIN VVVERBOSE

Then for maximum verbosity, duplicate every letter:

  EXPLAIN VVEERRBBOOSSEE


Uh, just kidding. I'm not partial to the duplicated keyword. Really.

                    - Thomas

Sun, 10 Mar 2002 21:23:42 -0800
Thomas Lockhart <thomas@fourpalms.org>
Re: Rationalizing EXPLAIN VERBOSE output
Thomas Lockhart wrote:
> ...
> > Yes, this was my initial proposal but Tom didn't like it.  Seemed very
> > clear to me.  Tom wants EXPLAIN VERBOSE VERBOSE.
> 
> Eh. Don't like that myself. How about adding V's to verbose? So
> 
>   EXPLAIN VERBOSE
>   EXPLAIN VVERBOSE
>   EXPLAIN VVVERBOSE
> 
> Then for maximum verbosity, duplicate every letter:
> 
>   EXPLAIN VVEERRBBOOSSEE
> 
> 
> Uh, just kidding. I'm not partial to the duplicated keyword. Really.

You had me going there for a while.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 11 Mar 2002 00:25:54 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Gavin Sherry wrote:
> On Mon, 11 Mar 2002, Bruce Momjian wrote:
> 
> > > I agree. This is fine under Unix, but command arguments are not really a
> > > grammar. Yacc doesn't enjoy terminal repetition and for good reason: it
> > > usually suggests a clumsy grammar. 
> > > 
> > > Personally, I think that Tom's code should go into standard EXPLAIN.
> > 
> > I am confused.  Which grammar do you like?
> 
> Neither =).

OK, would you suggest one?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 11 Mar 2002 00:34:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
On Mon, 11 Mar 2002, Bruce Momjian wrote:

> > I agree. This is fine under Unix, but command arguments are not really a
> > grammar. Yacc doesn't enjoy terminal repetition and for good reason: it
> > usually suggests a clumsy grammar. 
> > 
> > Personally, I think that Tom's code should go into standard EXPLAIN.
> 
> I am confused.  Which grammar do you like?

Neither =).

> > Is this overkill?
> 
> That was my initial reaction.  :-)

Fair enough.

Gavin


Mon, 11 Mar 2002 16:36:36 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Rationalizing EXPLAIN VERBOSE output
On Mon, 11 Mar 2002, Bruce Momjian wrote:

> Gavin Sherry wrote:
> > On Mon, 11 Mar 2002, Bruce Momjian wrote:
> > 
> > > > I agree. This is fine under Unix, but command arguments are not really a
> > > > grammar. Yacc doesn't enjoy terminal repetition and for good reason: it
> > > > usually suggests a clumsy grammar. 
> > > > 
> > > > Personally, I think that Tom's code should go into standard EXPLAIN.
> > > 
> > > I am confused.  Which grammar do you like?
> > 
> > Neither =).
> 
> OK, would you suggest one?

I don't think there needs to be a grammar change. I think that Tom's
qualification changes should go into non-verbose EXPLAIN and that pretty
vs. non-pretty debug just gets handled via debug_print_pretty.

The disadvantage of this is, of course, that users would want to be able
to change debug_print_pretty. I don't think that the solution to this is
another GUC variable though. I think it EXPLAIN output tables.

Yes, this results in a grammar change but IMHO users get a lot more out of
this modification than levels, since they can store/manipulate EXPLAIN
output if they choose. Naturally, there would be a psql \command tie in.

This is does some of what I want to get into a release some time in the
future: auditing. Perhaps storage of explain output would be more suited
to that. Just my 2 cents.

Gavin




Mon, 11 Mar 2002 16:55:45 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Rationalizing EXPLAIN VERBOSE output
>> I'm not partial to the duplicated keyword. Really.

Okay, okay, I concede.  "EXPLAIN VERBOSE n stmt" it is.

			regards, tom lane

Mon, 11 Mar 2002 01:18:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
> >> I'm not partial to the duplicated keyword. Really.
> Okay, okay, I concede.  "EXPLAIN VERBOSE n stmt" it is.

Another possibility is to implement

  SET VERBOSITY = n;

Why not do that and not bother extending/polluting the EXPLAIN syntax?

                 - Thomas

Mon, 11 Mar 2002 05:51:06 -0800
Thomas Lockhart <thomas@fourpalms.org>
Re: Rationalizing EXPLAIN VERBOSE output
Thomas Lockhart wrote:
> > >> I'm not partial to the duplicated keyword. Really.
> > Okay, okay, I concede.  "EXPLAIN VERBOSE n stmt" it is.
> 
> Another possibility is to implement
> 
>   SET VERBOSITY = n;
> 
> Why not do that and not bother extending/polluting the EXPLAIN syntax?

Unless you have another use for VERBOSITY, it seems like a waste.   I
don't see a value in moving control away from the EXPLAIN command
itself.  I realize it would be used as a default for all EXPLAIN
commands, but the level is just a single-digit number.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 11 Mar 2002 09:26:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Why not do that and not bother extending/polluting the EXPLAIN syntax?

> Unless you have another use for VERBOSITY, it seems like a waste.

For the moment, I plan to not touch the syntax; I'll follow Gavin's
suggestion of just putting the qual info into the default output.
If we really hate it after a month or two of looking at it, we can
figure out what kind of control knob to add then.

			regards, tom lane

Mon, 11 Mar 2002 09:38:53 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Why not do that and not bother extending/polluting the EXPLAIN syntax?
> 
> > Unless you have another use for VERBOSITY, it seems like a waste.
> 
> For the moment, I plan to not touch the syntax; I'll follow Gavin's
> suggestion of just putting the qual info into the default output.
> If we really hate it after a month or two of looking at it, we can
> figure out what kind of control knob to add then.

Sounds like a plan.  I can't imagine the new index clause being any more
complicated than what is already there.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 11 Mar 2002 10:11:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rationalizing EXPLAIN VERBOSE output
On Sun, Mar 10, 2002 at 11:52:49AM -0500, Tom Lane wrote:

> EXPLAIN VERBOSE select * from pg_class;
> 
> 	Add prettyprinted qual clauses
> 
> EXPLAIN VERBOSE VERBOSE select * from pg_class;
> 
> 	Add full plan-tree dump

I'd prefer having the non-prety-printed plan-tree dump moved off into
its own keyword. Eg:

EXPLAIN DUMP select * from pg_class;

The dump is sufficiently different from VERBOSE <n> output that it
should have its own keyword. Then the VERBOSE levels can just be used
for addition additional information to the pretty-printed tree and there
is no nasty shift from nice tree to ugly mess at some level.

Liam

-- 
Liam Stewart :: Red Hat Canada, Ltd. :: liams@redhat.com

Mon, 11 Mar 2002 10:24:51 -0500
Liam Stewart <liams@redhat.com>
Re: Rationalizing EXPLAIN VERBOSE output