Re: EXPLAIN omits schema?

On 6/13/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
> Magnus Hagander wrote:
> > On Wed, Jun 13, 2007 at 02:02:24PM +0100, Heikki Linnakangas wrote:
> >> 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.
>
> Sure you can. It's just a question of how complex the schema is :).
>
> > 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?
>
> I'm not sure. DB2 had real tables, but I found that a bit clumsy. It was
> nice because your old explain results were accumulated, but it was also
> not nice because of that same thing.
>
> One idea would be temporary tables.
>
> > Machine-readable is of course the main point - the exact format is more of
> > an implementation detail.
>
> Agreed.
>
> A potential problem is that as we add new node types etc., we need to
> extend the schema (whether it's a real relational schema or XML), and
> clients need to understand it. But I guess we already have the same
> problem with clients that parse the current explain output.

Oracle forces you (AFAIK) to create a set of tables to store explain
plan output, so when you EXPLAIN, it populates those tables, and then
you have to query to get it out. This is nice for admin tools that
have to parse the explain output, though it's obviously a pain for
explain-ing inside a command-line. An XML explain would be neat.

On a different sideline based on the original note of this thread,
much as EXPLAIN doesn't include the schema, \d doesn't include the
schema to describe INHERIT relationships in 8.2.4. If you have two
tables called PARENT, in two different schemas, and a child that
inherits from one of them, \d won't tell you which of the two it
inherits from.

- Josh