Thread: postgresql Explain command output

hi,
 
     is there any generalized format for the output for the output of the explain command ?. If so please send that generalized format to me.
otherwise tell me how to parse the output of explain command to
know where the relation name occurs,where the conditions occurs,
where the join conditions occur and so on. My  goal is create a visual
representation of the expain plan. Can some body help me in this regard.
         Thanks & regards
       RAVIRAM KOLIPAKA
      M.TECH(CSE)-final year
      IIIT Hyderabad   

On Thu, 6 Mar 2008, RaviRam Kolipaka wrote:

> My goal is create a visual representation of the expain plan.

This problem has been solved already by code that's in pgadmin and you
might look at that source code for hints if you want to write your own
implementation.  There's a good intro to using that at
http://www.postgresonline.com/journal/index.php?/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith@gregsmith.com> writes:
> On Thu, 6 Mar 2008, RaviRam Kolipaka wrote:
>> My goal is create a visual representation of the expain plan.

> This problem has been solved already by code that's in pgadmin and you
> might look at that source code for hints if you want to write your own
> implementation.

It's been solved more than once actually --- Red Hat did a "Visual
Explain" tool several years ago, which is unmaintained now but still
available for download (http://sources.redhat.com/rhdb/).  I've heard
that EDB picked it up and is now maintaining their own fork, but I
don't know the status of that for sure.  That code is in Java, if it
makes a difference to you.

            regards, tom lane

On Thu, 6 Mar 2008, Tom Lane wrote:

> Red Hat did a "Visual Explain" tool several years ago, which is
> unmaintained now but still available for download
> (http://sources.redhat.com/rhdb/).  I've heard that EDB picked it up and
> is now maintaining their own fork, but I don't know the status of that
> for sure.

I know I wrote this down somewhere...ah ha, it was in the MySQL
comparision paper:

Visual Explain, originally a RedHat component that has been kept current
and improved by Enterprise DB, comes bundled with the EnterpriseDB
Advanced Server package.  It can be built to run against other PostgreSQL
installations using the source code to their Developer Studio package:
http://www.enterprisedb.com/products/download.do

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

 How do we know in the output of expain command table  or constraint names so that while parsing  each line of the output
 we can able to recognise them and build the pictorial representation. for example if you consider the following output
 
              EXPLAIN select * from table1,table2 where roll=id_no;
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=29.12..59.31 rows=850 width=124)
   Hash Cond: (table2.id_no = table1.roll)
   ->  Seq Scan on table2  (cost=0.00..18.50 rows=850 width=62)
   ->  Hash  (cost=18.50..18.50 rows=850 width=62)
         ->  Seq Scan on table1  (cost=0.00..18.50 rows=850 width=62)
 
       we can know the table name , just by seeing any string after ON keyword . Like that how to recognise the other constraints and so on.