Обсуждение: EXPLAIN Node Docs?

Hackers,

Is there any place where the contents of EXPLAIN nodes are documented? I'm making a lot of use of the XML format right
nowto figure out what queries sort on what tables and with what columns, but I've had to do a lot of experimentation to
figureout what each type of node contains. 

For example, today I wanted to know what columns were being used in GROUP BY expressions. I poked around our client's
samplequeries till I found one with a GROUP BY expression and EXPLAINed it. It turns out that the Group node doesn't
talkabout columns, but has a Sort subplan node that does. On reflection, this makes sense, and so I just collected data
forall Sort nodes (it wasn't just GROUP BY sorting I needed) got get my results. 

But it would have been a lot easier if the schema for various nodes was documented. I'd like to be able to just look up
thedocumentation for various nodes to see what elements they contain and what subnodes they might have. Would make it a
loteasier. Does such a thing exist? 

On a related note, how difficult would it be to add a list of column names to nodes with Filter elements? Right now I'm
extractingcolumn names by calling regexp_matches() against the filter element, which is kind of brute force. As an
exmple,right now I have 
        <Plan>                                                                           +          <Node-Type>Index
Scan</Node-Type>                                             +
<Parent-Relationship>Outer</Parent-Relationship>                              +
<Scan-Direction>Forward</Scan-Direction>                                      +
<Index-Name>idx_stuff</Index-Name>               +          <Relation-Name>activities</Relation-Name>
                  +          <Alias>a</Alias>                                                               +
<Startup-Cost>0.00</Startup-Cost>                                             +          <Total-Cost>27.97</Total-Cost>
                                               +          <Plan-Rows>1</Plan-Rows>
                +          <Plan-Width>36</Plan-Width>                                                    +
<Index-Cond>(person_id= 78323)</Index-Cond>                                   +          <Filter>(((SubPlan 1) > 0)
OR(category_id <> 15))</Filter>           + 

Would be nice if there was an additional element with something like
         <Columns>category_id</Columns>

Thanks,

David

"David E. Wheeler" <david@kineticode.com> writes:
> Is there any place where the contents of EXPLAIN nodes are documented?

Use the source, Luke ...

In particular, src/include/nodes/plannodes.h is pretty well commented.
If it's not immediately obvious how that maps to what's shown by
EXPLAIN, look into commands/explain.c.  (There are things that aren't
mentioned by EXPLAIN even in verbose mode, such as which columns Group
is going to group by; usually that's because it's redundant with info
available elsewhere, such as the sort columns of a lower sort node.)

Keep in mind also that we reserve the right to whack these things around
in arbitrary ways from one release to the next.
        regards, tom lane

On Apr 29, 2011, at 2:07 PM, Tom Lane wrote:

> In particular, src/include/nodes/plannodes.h is pretty well commented.

Ah, that's a useful file to scan, thanks.

> If it's not immediately obvious how that maps to what's shown by
> EXPLAIN, look into commands/explain.c.

Yeah, that's the file I have been reading.

> (There are things that aren't
> mentioned by EXPLAIN even in verbose mode, such as which columns Group
> is going to group by; usually that's because it's redundant with info
> available elsewhere, such as the sort columns of a lower sort node.)

Right, I figured that one out. Same does not appear to be true of Filter elements, though.

> Keep in mind also that we reserve the right to whack these things around
> in arbitrary ways from one release to the next.

Yeah, though I assume you mean major releases, not maintenance releases. Still, it'd be nice to have a documentation
reference.

Best,

David