Re: generic options for explain

On Mon, May 25, 2009 at 10:55:48AM -0400, Tom Lane wrote:
> Joshua Tolley <eggyknap(at)gmail(dot)com> writes:
> > The Oracle version, as it fills the table of explain results, gives
> > each number an id and the id of its parent row, which behavior we
> > could presumably copy. I'm definitely keen to keep a human-readable
> > EXPLAIN such as we have now, to augment the table-based proposal, but
> > a table would provide the more flexible output we'd need for more
> > detailed reporting, a simple interface for applications to consume the
> > EXPLAIN data without human intervention, and a convenient platform
> > from whence the data can be transformed to XML, JSON, etc. for those
> > that are so inclined.
>
> I would think a table would be considerably *less* flexible --- you
> could not easily change the output column set. Unless you're imagining
> just dumping something equivalent to the current output into a text
> column. Which would be flexible, but it'd hardly have any of the
> other desirable properties you list.

I'm not sure I see why it would be less flexible. I'm imagining we define some
record type, and a function that returns a set of those records. The fields in
the record would include data element this version of explain could possibly
return. Then you call a function to explain a query, passing it some options
to select which of those data elements you're interested in. The function
returns the same data type at each call, filling with NULLs the fields you've
told it you're uninterested in. Changes between versions would modify this
data type, but provided consumer applications have specified the columns
they're interested in (rather than, say, SELECT *) that shouldn't bother
anyone.

The functions to change this into some other format would probably need to be
more intelligent than just that. That seems a fair price to pay.

- Josh / eggyknap