Re: EXPLAIN omits schema?

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com