Re: [BUGS] Invalid YAML output from EXPLAIN

Tom Lane wrote:
> This doesn't look amazingly unlike the current JSON output,
> and to the extent that we have to add more quoting to it, it's
> going to look even more like the JSON output.
>   

I don't know about that; here's the JSON one:

EXPLAIN (FORMAT JSON) SELECT * FROM customers WHERE customerid>1000 
ORDER BY zip;               QUERY PLAN                
-------------------------------------------[                                        +  {
     +    "Plan": {                            +      "Node Type": "Sort",               +      "Startup Cost":
4449.30,          +      "Total Cost": 4496.80,             +      "Plan Rows": 19000,                +      "Plan
Width":268,                 +      "Sort Key": ["zip"],               +      "Plans": [                         +
{                                +          "Node Type": "Seq Scan",       +          "Parent Relationship": "Outer",+
       "Relation Name": "customers",  +          "Alias": "customers",          +          "Startup Cost": 0.00,
 +          "Total Cost": 726.00,          +          "Plan Rows": 19000,            +          "Plan Width": 268,
      +          "Filter": "(customerid > 1000)"+        }                                +      ]
           +    }                                    +  }                                      +]
 
From the perspective of how that's less useful as a human form of 
output, it's longer, wider, and has redundant punctuation that gets in 
the way.

I think that YAML quoting will need to respect one of the special cases 
to keep from ruining its readability:  "Requires quoting only if the 
first character" for " will make its current format look terrible if 
that rule is applied to the whole line instead.  That sounds like a 
necessary special case to include:  don't quote any quote characters 
that appear unless they're the first character on the line.  Everything 
else could switch back to really aggressive quoting in every spot and 
that wouldn't hurt the readability of the format very much IMHO.

> Given the lack of any field separators other than newlines, I'm also
> finding myself extremely doubtful about the claim that it survives
> line-wrapping mutilations well.

All I was claiming there is that the output is dramatically less wide 
than the standard text format of the same plan, and therefore far less 
likely to get nailed by a mail client that wraps at normal line widths.  
Agreed that once wrapping does occur, it has serious problems too.

Here are the stats for this plan, leaving off the QUERY PLAN header from 
each:

TEXT:  4 vertical, 69 horizontal
YAML:  18 vertical, 36 horizontal
JSON:  25 vertical, 43 horizontal
XML[1]:  27 vertical, 60 horizontal

Quote the TEXT line with "> " or get a plan with one more line of 
intendation, and you're likely to get wrapped badly at the 72 character 
line limit some clients use.  Quite a bit more headroom before the YAML 
format will wrap like that; JSON is in the middle.

I now see plenty of use for YAML when exchanging plans over e-mail, and 
it's a bonus that should survive that format to be parseable on the 
other side.   JSON and XML are certainly the preferred way to feed plans 
into analysis tools. unambiguously.

[1] Might as well make this a complete example:
<explain xmlns="http://www.postgresql.org/2009/explain">  +  <Query>                                                 +
 <Plan>                                                +      <Node-Type>Sort</Node-Type>                         +
<Startup-Cost>4449.30</Startup-Cost>                +      <Total-Cost>4496.80</Total-Cost>                    +
<Plan-Rows>19000</Plan-Rows>                       +      <Plan-Width>268</Plan-Width>                        +
<Sort-Key>                                         +        <Item>zip</Item>                                  +
</Sort-Key>                                        +      <Plans>                                             +
<Plan>                                           +          <Node-Type>Seq Scan</Node-Type>                 +
<Parent-Relationship>Outer</Parent-Relationship>+         <Relation-Name>customers</Relation-Name>        +
<Alias>customers</Alias>                       +          <Startup-Cost>0.00</Startup-Cost>               +
<Total-Cost>726.00</Total-Cost>                +          <Plan-Rows>19000</Plan-Rows>                    +
<Plan-Width>268</Plan-Width>                   +          <Filter>(customerid > 1000)</Filter>         +
</Plan>                                          +      </Plans>                                            +
</Plan>                                              +  </Query>
+</explain>

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us