Re: EXPLAIN omits schema?

On Wed, Jun 13, 2007 at 02:02:24PM +0100, Heikki Linnakangas wrote:
> Magnus Hagander wrote:
> >On Wed, Jun 13, 2007 at 01:20:25PM +0100, Dave Page wrote:
> >>>>Just adding the schema name seems the most sensible and usable option -
> >>>>not to mention the easiest!
> >>>While completely ignoring the current behaviour and likely the reason
> >>>it's done the way it is now... explain output was, and still is
> >>>primairly, for humans to read.
> >>Humans deserve schemas as well!! :-). As for the likely reason for the
> >>current behaviour, well, I'd rather have precise,
> >>non-potentially-ambiguous info than save a few characters.
> >
> >Just to open a whole new can of worms ;-)
> >
> >I read an article a couple of days ago about the "machine readable showplan
> >output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
> >format). It does make a lot of sense if yourp rimary interface is !=
> >commandline (psql), such as pgadmin or phppgadmin. The idea being that you
> >can stick in *all* the details you want, since you can't possibly clutter
> >up the display. And you stick them in a well-defined XML format (or another
> >format if you happen to hate XML) where the client-side program can easily
> >parse out whatever it needs. It's also future-proof - if you add a new
> >field somewhere, the client program parser won't break.
> >
> >Something worth doing? Not to replace the current explain output, but as a
> >second option (EXPLAIN XML whatever)?
>
> I agree it would be nice to have machine readable explain output.
>
> DB2 has the concept of "explain tables". Explain output is written to
> tables, which tools query and pretty print the output. I like that idea
> in principle. PostgreSQL is a relational database, so having the explain
> output in relations make sense. No need for XML or any other extra
> libraries, in either the server or client. Having the data in relational
> format allows you to query them. For example, show me all sequential
> scans, or all nodes where the estimated number of rows is off by a
> certain factor.

Assuming you can actually *represent* the whole plan as tables, that would
of course work fine. But I assume you mean "virtual tables"? So I do
EXPLAIN whatever, and get back one or more resultssets with the data? Or do
they write it to *actual* tables in the database?

Machine-readable is of course the main point - the exact format is more of
an implementation detail.

//Magnus