On Sun, May 24, 2009 at 06:53:29PM -0400, Tom Lane wrote:
> Greg Smith <gsmith@gregsmith.com> writes:
> > On Sun, 24 May 2009, Pavel Stehule wrote:
> >> we should have a secondary function explain_query(query_string,
> >> option) that returns setof some.
>
> > +1. The incremental approach here should first be adding functions that
> > actually do the work required. Then, if there's a set of those that look
> > to be extremely useful, maybe at that point it's worth talking about how
> > to integrate them into the parser. Starting with the parser changes
> > rather than the parts that actually do the work is backwards. If you do
> > it the other way around, at all times you have a patch that actually
> > provides immediate useful value were it to be committed.
>
> > Something that returns a setof can also be easily used to implement the
> > "dump EXPLAIN to a table" feature Josh Tolley brought up (which is another
> > common request in this area).
>
> A serious problem with EXPLAIN via a function returning set, or with
> putting the result into a table, is that set results are logically
> unordered, just as table contents are. So from a strict point of view
> this only makes sense when the output format is designed to not depend
> on row ordering to convey information. We could certainly invent such
> a format, but I think it's a mistake to go in this direction for
> EXPLAIN output that is similar to the current output.
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.
- Josh / eggyknap
