Re: [HACKERS] 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 &gt; 1000)</Filter> +
</Plan> +
</Plans> +
</Plan> +
</Query> +
</explain>

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us