Thread: Anyone for prettyprinted EXPLAIN VERBOSE?
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
:basetype23 :aggtype 23 :target { VAR :varno 0 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno1} :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
:resjunkfalse } :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
:basetype23 :aggtype 23 :target { VAR :varno 0 :varattno 1
:vartype23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1
} :usenulls false } } ) :qpqual <> :lefttree { SEQSCAN :cost 1.165
:size5 :width 4 :state <> :qptargetlist ( { TARGETENTRY :resdom { RESDOM
:resno 1 :restype 23 :restypmod -1 :resname "<>" :reskey 0
:reskeyop0 :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
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
:basetype23 :aggtype 23 :target { VAR :varno 0 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno1} :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
:resjunkfalse } :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,
Pennsylvania19026
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
:basetype23 :aggtype 23 :target { VAR :varno 0 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno1} :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
:resjunkfalse } :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) #
> 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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
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
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
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
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
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