Skip site navigation (1) Skip section navigation (2)

EXPLAIN Node Docs?

From: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: EXPLAIN Node Docs?
Date: 2011-04-29 18:56:18
Message-ID: 8481E6DC-CFF8-4E1A-ADE4-52ED8691C7B8@kineticode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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 now to figure out what queries sort on what tables and with what columns, but I've had to do a lot of experimentation to figure out 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 sample queries till I found one with a GROUP BY expression and EXPLAINed it. It turns out that the Group node doesn't talk about columns, but has a Sort subplan node that does. On reflection, this makes sense, and so I just collected data for all 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 the documentation for various nodes to see what elements they contain and what subnodes they might have. Would make it a lot easier. 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 extracting column 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) &gt; 0) OR (category_id &lt;&gt; 15))</Filter>           +

Would be nice if there was an additional element with something like

          <Columns>category_id</Columns>

Thanks,

David



Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2011-04-29 19:36:17
Subject: Re: "stored procedures"
Previous:From: Dimitri FontaineDate: 2011-04-29 18:50:38
Subject: Re: Extreme bloating of intarray GiST indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group