Re: machine-readable explain output

On Sat, Jun 13, 2009 at 9:08 AM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
> On Saturday 13 June 2009 01:10:06 Robert Haas wrote:
>> <pgexplain>, as it happens...  I could post some samples of the
>> output, but it seems like it might be just as well to let those who
>> are curious try it for themselves.  I'd rather get opinions from
>> people who care enough to download & test than from those who are just
>> bikeshedding.  :-)
>
> I recommend, however, that you think about writing a regression test for this,
> so the interfaces are explicit, and those tweaking them in the future know
> what they are dealing with.

I would like to have something in this area, but Tom didn't think it
was workable.

http://archives.postgresql.org/message-id/603c8f070904151623ne07d744k615edd4aa669a64a@mail.gmail.com

Currently, we don't even have something trivial like "EXPLAIN SELECT
1" in the regression tests, so even if you completely break EXPLAIN so
that it core dumps (voice of experience speaking here) make check
still passes with flying colors.

One feature I'd like to add is an EXPLAIN-option for "COSTS", so that
you can say explain (costs off) .... Then we could at least try a
couple of simple examples against the build-farm to see whether the
issues that Tom is worried about are problems in practice and to what
degree. But I'm a little reluctant to develop that until at least
some of my existing work is committed, because at present I have no
guarantee either that this patch will be accepted or that it won't be
extensively modified by the committer, thus creating merge conflicts
for me to resolve. However, assuming the infrastructure in the
explain_options patch is accepted in something similar to its current
form, it should be a very easy patch to write when the time comes.

> A couple of comments on the specifics of the output:
>
> For the JSON format:
>
> * Numbers should not be quoted.

OK, will fix.

> For the XML format:
>
> * Instead of <pgexplain>, use <explain> with an XML namespace declaration.

Could you specify this a bit further, like write out exactly what you
want it to look like? My XML-fu is not very strong.

> The schema name is missing in either output format.  I think that was supposed
> to be one of the features of this that the objects are unambiguously
> qualified.

Well, as I said, I'm not sure that this decision should be made based
on the selected output format. I think it should be controlled by a
separate option that can be used for text, XML, or JSON. Of course,
we also don't want to end up with a zillion options. I think maybe
the existing VERBOSE option could be pressed into service here. Right
now, all it does is print out the output lists for each node, but
maybe it could also have the effect of forcing the schema name to be
emitted, and any other similarly minor verbosities we run across.

There's other weirdness in this area too: when emitting a qual, we
table-qualify column names according to a complex heuristic (for scan
quals, when the outer plan is non-NULL or it's a subquery scan; for
upper quals, when the length of the range-table list is more than 1).
Not sure whether anyone cares about this or not. In a similar vein,
in report_triggers(), we omit the constraint name if there is a
trigger name. All of these seem like fairly good candidates for
things that you might want to behave differently if you ask for
"VERBOSE".

> I'm not sure I like element names such as <Node-Type>, instead of say
> <nodetype>, which is more like HTML and DocBook.  (Your way might be more like
> SOAP, I guess.)

I'm not sure I like them either. I mostly did it that way because I
wanted to maintain consistency with the text output, which uses labels
like "Hash Cond" and "Filter". So I just made the JSON format use
those same labels, and for the XML format, since tag names can't
contain spaces, I just replaced spaces with dashes. Once I made that
decision it seemed like everything else should be consistent, so
that's what I did. But we could certainly subject them all to some
additional regular transformation if we're so inclined. I'm not sure
it's really worth the additional code complexity, but I don't care
very much.

> Also, the result type of an EXPLAIN (format xml) should be type xml, not text.

Seems reasonable. I'll see if I can figure out how to do that.

> In general, I like this direction very much.  There will probably be more
> tweaks on the output format over time.  It's not like the plain EXPLAIN hasn't
> been tweaked countless times.

Cool, thanks for the review. I have no illusions it won't get changed
further. In all honesty, I'm most interested in the options syntax.
The multiple output formats portion is just a demonstration that you
can use the options syntax to enable interesting functionality, but I
personally have little use for it. I'm hoping, however, that once we
have a standard way to add options, people will propose more options
that do interesting things; I have a few ideas myself.

...Robert