When you do an EXPLAIN VERBOSE, two different representations of the
query plan are produced.  The client sees something like this:

regression=> explain verbose select sum(f1) from int4_tbl;
NOTICE:  QUERY DUMP:

{ AGG :cost 1.165 :size 5 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname "sum" :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname sum :basetype 23 :aggtype 23 :target { VAR :varno 0 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} :usenulls false }}) :qpqual <> :lefttree { SEQSCAN :cost 1.165 :size 5 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0 }

but in the postmaster log we format it like this:

NOTICE:  QUERY PLAN:

Aggregate  (cost=1.16 rows=5 width=4)
  ->  Seq Scan on int4_tbl  (cost=1.16 rows=5 width=4)

{ AGG 
   :cost 1.165 
   :size 5 
   :width 4 
   :state <> 
   :qptargetlist (
      { TARGETENTRY 
      :resdom 
         { RESDOM 
         :resno 1 
         :restype 23 
         :restypmod -1 
         :resname "sum" 
         :reskey 0 
         :reskeyop 0 
         :ressortgroupref 0 
         :resjunk false 
         }
       
      :expr 
         { AGGREG 
         :aggname sum 
         :basetype 23 
         :aggtype 23 
         :target 
            { VAR 
            :varno 0 
            :varattno 1 
            :vartype 23 
            :vartypmod -1  
            :varlevelsup 0 
            :varnoold 1 
            :varoattno 1
            }
          
         :usenulls false 
         }
      }
   )
    
   :qpqual <> 
   :lefttree 
      { SEQSCAN 
      :cost 1.165 
      :size 5 
      :width 4 
      :state <> 
      :qptargetlist (
         { TARGETENTRY 
         :resdom 
            { RESDOM 
            :resno 1 
            :restype 23 
            :restypmod -1 
            :resname "<>" 
            :reskey 0 
            :reskeyop 0 
            :ressortgroupref 0 
            :resjunk false 
            }
          
         :expr 
            { VAR 
            :varno 1 
            :varattno 1 
            :vartype 23 
            :vartypmod -1  
            :varlevelsup 0 
            :varnoold 1 
            :varoattno 1
            }
         }
      )
       
      :qpqual <> 
      :lefttree <> 
      :righttree <> 
      :extprm ()
       
      :locprm ()
       
      :initplan <> 
      :nprm 0  
      :scanrelid 1 
      }
    
   :righttree <> 
   :extprm ()
    
   :locprm ()
    
   :initplan <> 
   :nprm 0 
   }

Does anyone think that the first form has any conceivable use?  I would
like to get rid of it and deliver the prettyprinted format to both log
and client.  I think it may have been done this way because old versions
of the backend didn't cope very gracefully with sending long NOTICE
messages to the client, but that constraint is history...

			regards, tom lane

Fri, 17 Dec 1999 18:34:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Anyone for prettyprinted EXPLAIN VERBOSE?

I remember Jan saying he liked the compressed one.


> When you do an EXPLAIN VERBOSE, two different representations of the
> query plan are produced.  The client sees something like this:
> 
> regression=> explain verbose select sum(f1) from int4_tbl;
> NOTICE:  QUERY DUMP:
> 
> { AGG :cost 1.165 :size 5 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname "sum" :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname sum :basetype 23 :aggtype 23 :target { VAR :varno 0 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} :usenulls false }}) :qpqual <> :lefttree { SEQSCAN :cost 1.165 :size 5 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0 }
> 
> but in the postmaster log we format it like this:
> 
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=1.16 rows=5 width=4)
>   ->  Seq Scan on int4_tbl  (cost=1.16 rows=5 width=4)
> 
> { AGG 
>    :cost 1.165 
>    :size 5 
>    :width 4 
>    :state <> 
>    :qptargetlist (
>       { TARGETENTRY 
>       :resdom 
>          { RESDOM 
>          :resno 1 
>          :restype 23 
>          :restypmod -1 
>          :resname "sum" 
>          :reskey 0 
>          :reskeyop 0 
>          :ressortgroupref 0 
>          :resjunk false 
>          }
>        
>       :expr 
>          { AGGREG 
>          :aggname sum 
>          :basetype 23 
>          :aggtype 23 
>          :target 
>             { VAR 
>             :varno 0 
>             :varattno 1 
>             :vartype 23 
>             :vartypmod -1  
>             :varlevelsup 0 
>             :varnoold 1 
>             :varoattno 1
>             }
>           
>          :usenulls false 
>          }
>       }
>    )
>     
>    :qpqual <> 
>    :lefttree 
>       { SEQSCAN 
>       :cost 1.165 
>       :size 5 
>       :width 4 
>       :state <> 
>       :qptargetlist (
>          { TARGETENTRY 
>          :resdom 
>             { RESDOM 
>             :resno 1 
>             :restype 23 
>             :restypmod -1 
>             :resname "<>" 
>             :reskey 0 
>             :reskeyop 0 
>             :ressortgroupref 0 
>             :resjunk false 
>             }
>           
>          :expr 
>             { VAR 
>             :varno 1 
>             :varattno 1 
>             :vartype 23 
>             :vartypmod -1  
>             :varlevelsup 0 
>             :varnoold 1 
>             :varoattno 1
>             }
>          }
>       )
>        
>       :qpqual <> 
>       :lefttree <> 
>       :righttree <> 
>       :extprm ()
>        
>       :locprm ()
>        
>       :initplan <> 
>       :nprm 0  
>       :scanrelid 1 
>       }
>     
>    :righttree <> 
>    :extprm ()
>     
>    :locprm ()
>     
>    :initplan <> 
>    :nprm 0 
>    }
> 
> Does anyone think that the first form has any conceivable use?  I would
> like to get rid of it and deliver the prettyprinted format to both log
> and client.  I think it may have been done this way because old versions
> of the backend didn't cope very gracefully with sending long NOTICE
> messages to the client, but that constraint is history...
> 
> 			regards, tom lane
> 
> ************
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Fri, 17 Dec 1999 19:33:20 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE?
Bruce Momjian wrote:

> I remember Jan saying he liked the compressed one.

    Change it if you want to.

    The  reason why I prefer the nodeToString() format is bacause
    (even if it might look like garbage to someone) for  me  it's
    the  only  way to LOOK if the right node went from the parsed
    state  into  the  rewritten  state.  Or  if  an  OLD/NEW  got
    rewritten  correctly  into  the  scan  relation's/TLE's  one.
    That's impossible if having the prettyprint only, because for
    non-trivial   trees   the  output  occurs  hundreds  (if  not
    thousands) of lines later, especially if  multi-action  rules
    or  subselects  get involved. Not to tell about recursive and
    conditional rules  getting  applied  (note  that  conditional
    instead  rules  put  out two trees, one with the negated rule
    qual and the original action, one  with  the  original  rules
    qual  and action). To compare in such a case, if anything was
    done well, really requires to  look  at  rewriter  input  AND
    output.

    I  have  a  scrollback buffer of 2000 lines on my XTerm icon,
    and it already happened that I wasn't able to scroll back  to
    the wanted location WHILE USING COMPRESSED FORMAT ONLY!

    But  if  we  get  our  hands on the parsetree overhaul, I can
    insert my own "printf(...nodeToString())" statements into the
    places,  where  I  really  need  to  look  at. That's usually
    another place, than these messages are coming from.

    I'm only in doubt about if anyone at all DOES use the  pretty
    printed  version  for  anything.  I assume I'm not too bad in
    reading printed parsetrees, but whenever the  pretty  printed
    tree  exceeds some hundreds of lines, I'm totally lost and am
    unable to find the location I'm looking for (what I easily do
    when  looking  at  the compressed format). I allways wondered
    why the pretty print was implemented at all.

    Who really USES the explanative format  to  debug  things  on
    non-trivial queries?

    Since Tom is asking, I assume at least he's the one who does.
    But then again, he must be able to  see  his  target  station
    expressed  in some Expr-, Oper- and Func- nodes while pushing
    the buttons to get an underground-ticket. So  who  else  does
    like  the  pretty  printed  version better for non-esthetical
    reasons?


Jan

>
>
> > When you do an EXPLAIN VERBOSE, two different representations of the
> > query plan are produced.  The client sees something like this:
> >
> > regression=> explain verbose select sum(f1) from int4_tbl;
> > NOTICE:  QUERY DUMP:
> >
> > { AGG :cost 1.165 :size 5 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname "sum" :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname sum :basetype 23 :aggtype 23 :target { VAR :varno 0 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} :usenulls false }}) :qpqual <> :lefttree { SEQSCAN :cost 1.165 :size 5 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname "<>" :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0 }
> >
> > but in the postmaster log we format it like this:
> >
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=1.16 rows=5 width=4)
> >   ->  Seq Scan on int4_tbl  (cost=1.16 rows=5 width=4)
> >
> > { AGG
> >    :cost 1.165
> >    :size 5
> >    :width 4
> >    :state <>
> >    :qptargetlist (
> >       { TARGETENTRY
> >       :resdom
> >          { RESDOM
> >          :resno 1
> >
> > [...]
> >
> >    :righttree <>
> >    :extprm ()
> >
> >    :locprm ()
> >
> >    :initplan <>
> >    :nprm 0
> >    }
> >
> > Does anyone think that the first form has any conceivable use?  I would
> > like to get rid of it and deliver the prettyprinted format to both log
> > and client.  I think it may have been done this way because old versions
> > of the backend didn't cope very gracefully with sending long NOTICE
> > messages to the client, but that constraint is history...


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Sat, 18 Dec 1999 02:47:20 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE?
>     Who really USES the explanative format  to  debug  things  on
>     non-trivial queries?
> 
>     Since Tom is asking, I assume at least he's the one who does.
>     But then again, he must be able to  see  his  target  station
>     expressed  in some Expr-, Oper- and Func- nodes while pushing
>     the buttons to get an underground-ticket. So  who  else  does
>     like  the  pretty  printed  version better for non-esthetical
>     reasons?
> 

I prefer pretty-print.  I view it in the server log files, and go node
by node until I find the problem.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Fri, 17 Dec 1999 20:59:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE?
At 02:47 AM 12/18/99 +0100, Jan Wieck wrote:

>    Since Tom is asking, I assume at least he's the one who does.
>    But then again, he must be able to  see  his  target  station
>    expressed  in some Expr-, Oper- and Func- nodes while pushing
>    the buttons to get an underground-ticket. So  who  else  does
>    like  the  pretty  printed  version better for non-esthetical
>    reasons?

Well, it's a lot more readable for newcomers who are interested in
learning how their queries are being turned into plans.  Such newcomers
might become helpful people as time goes on and as they have time in
their life to become contributors...

It probably wouldn't be that hard to add a "jan" command that would
do a non-pretty-printed explain verbose for those who want it, would
it?

As far as X limitations on storing lines, for debugging clearly you
want to be able to dump stuff into files regardless of pretty or
"ugly" formatting...it wouldn't be difficult to modify psql to dump
explanations into a file directly, would it?

Dumping thousands of lines of either ugly or pretty plan dumps onto
a terminal is ... well ... terminal.  You shouldn't have to do that.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Fri, 17 Dec 1999 19:39:40 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE?
wieck@debis.com (Jan Wieck) writes:
>     I'm only in doubt about if anyone at all DOES use the  pretty
>     printed  version  for  anything.  I assume I'm not too bad in
>     reading printed parsetrees, but whenever the  pretty  printed
>     tree  exceeds some hundreds of lines, I'm totally lost and am
>     unable to find the location I'm looking for (what I easily do
>     when  looking  at  the compressed format). I allways wondered
>     why the pretty print was implemented at all.

To each his own poison, I guess.  Reverse the above one hundred eighty
degrees, and it's my opinions ;-).  But if you like the compressed
layout better, sure, we can keep supporting it.  How about we implement
a SET VARIABLE control to select compact or pretty-printed mode, but
still send the same format to both postmaster log and client?  My main
gripe is there's no way at present to see the pretty-printed mode
without going to the postmaster log, which might not be readily
available to ordinary users.

(Actually, it's not clear to me why the postmaster log should get
these entries at all; for the most part it's just waste of log
space to send EXPLAIN outputs to the log...)

>     So who else does like the pretty printed version better for
>     non-esthetical reasons?

Uh, esthetics is everything in this case, isn't it?  Either you
find the format pleasing/readable, or not.

			regards, tom lane

Sat, 18 Dec 1999 00:30:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE? 
EXPLAIN VERBOSE COMPRESSED

On 1999-12-17, Bruce Momjian mentioned:

> I remember Jan saying he liked the compressed one.
> 
> > When you do an EXPLAIN VERBOSE, two different representations of the
> > query plan are produced.  The client sees something like this:

> > Does anyone think that the first form has any conceivable use?  I would


-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




Sat, 18 Dec 1999 14:06:56 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE?
On 1999-12-18, Tom Lane mentioned:

> (Actually, it's not clear to me why the postmaster log should get
> these entries at all; for the most part it's just waste of log
> space to send EXPLAIN outputs to the log...)

Maybe it shouldn't be a notice in the first place?

Actually, something unrelated (must have been the notices popping up in
the regression tests) led me to the idea of redirecting notice output into
the regular query output channel in psql, which would make it subject to
the pager if you have one set up. That would help those complaining about
100s of lines coming down their terminal. On my todo list.

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Sat, 18 Dec 1999 17:13:11 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE? 
Peter Eisentraut <peter_e@gmx.net> writes:
> Actually, something unrelated (must have been the notices popping up in
> the regression tests) led me to the idea of redirecting notice output into
> the regular query output channel in psql, which would make it subject to
> the pager if you have one set up. That would help those complaining about
> 100s of lines coming down their terminal. On my todo list.

This may be a bad idea.  There are many people who use psql in shell
scripts, and for them it is critical that non-data messages like NOTICEs
get sent to stderr, *not* mixed in with query results on stdout.

If you can arrange to page stderr output, cool...

			regards, tom lane

Sat, 18 Dec 1999 16:57:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE? 
On 1999-12-18, Tom Lane mentioned:

> If you can arrange to page stderr output, cool...

I had that experience already, but the two pagers (one for stdin, one for
stderr) didn't get along so well ... ;) I guess if you want this
functionality you could start psql with 2>1 or whatever it was.

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Mon, 20 Dec 1999 01:18:52 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Anyone for prettyprinted EXPLAIN VERBOSE? 