Re: EXPLAIN format changes

Hi Dave,

Dave Page wrote:

Hi Ashesh,

Tom made some changes to the EXPLAIN output
(http://archives.postgresql.org//pgsql-hackers/2009-04/msg00274.php).
Please can you check if the graphical explain tool in pgAdmin is
affected, and if so, make the necessary changes.

I have started looking into this new format change.
This has introduced the SubPlan(s) and with its name in the explain output.

This leads to some problem in our current implementation in showing explain:
* SubPlan titles are getting appended as conditions in the previous shapes (steps).
* We may need to introduce a new shape (SubPlan) with title - probably a box
  containing all the steps (shapes) under it.
* This leads to a lot of code change and testing. :(

I have created two figures for the given example.
* Fig.1 displays the current explain view.
* Fig.2 displays the proposed explain view.

While drawing the below proposed explain figure, I started thinking about
"How should we implement this?". First thought comes in to mind is - it is a lot
of work and can introduce new bugs (which we would not like to have in beta version)
So, we can leave this for 1.11 for right now.

And, just remove the red colored SubPlans from the first figure.

What do you say?

Please go through the proposed explain view for pgAdminIII 1.11.

Taking this example in to consideration:
------------------------- QUERY ------------------------------------------------
with wumpus as (select * from tenk1 )
select *
from wumpus
where unique2 = (select sum(f1) from int4_tbl)
   or exists(select 1 from tenk1 z where z.unique1 = wumpus.thousand);

------------------------- EXPLAIN ----------------------------------------------
CTE Scan on wumpus  (cost=446.07..83355.67 rows=5025 width=244)
  Filter: ((unique2 = $1) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  CTE wumpus
    ->  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)
  InitPlan 2 (returns $1)
    ->  Aggregate  (cost=1.06..1.07 rows=1 width=4)
          ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)
  SubPlan 3
    ->  Index Scan using tenk1_unique1 on tenk1 z  (cost=0.00..8.27 rows=1 width=0)
          Index Cond: (unique1 = $2)
  SubPlan 4
    ->  Seq Scan on tenk1 z  (cost=0.00..445.00 rows=10000 width=4)

_______________________________ CURRENT EXPLAIN VIEW ________________________________________________________________
                     ------------------------                                     ---------------------------------
                     |Seq Scan on tenk1     |                                     |CTE Scan on wumpus             |
                     |InitPlan 2 (return $1)|---------------------===============>|Filter: ((unique2 = $1) OR ... |
                     |(cost=0.00...         |                     |   |   |       ---------------------------------
                     ------------------------                     |   |   |
 ----------------------         ---------------                   |   |   |
 |Seq Scan on int4_tbl|         |Aggregate    |                   |   |   |
 |SubPlan 3           |-------> |             | ------------------|   |   |
 |(cost=0.00...       |         |(cost=0.00...|                       |   |
 ----------------------         ---------------                       |   |
                     --------------------------------------           |   |
                     |Index Scan using tenk1_unique1      |           |   |
                     |on tenk1 z                          |           |   |
                     |Index Cond: (unique1 = $2) SubPlan 4|------------   |
                     |(cost=0.00...                       |               |
                     --------------------------------------               |
                     ---------------------                                |
                     |Seq Scan on tenk1 z|                                |
                     |(cost=0.00...      |--------------------------------|
                     ---------------------
_______________________________________________________________________________________________________________________

NOTE:
In above example, the red colored are the Sub-Plan(s) for the below shapes (steps).

________________________________ PROPOSED EXPLAIN VIEW ________________________________________________________________
                    -------- CTE wumpus --------
                    | ------------------------ |                                    ---------------------------------
                    | |Seq Scan on tenk1     | |                                    |CTE Scan on wumpus             |
                    | |                      |-|--------------------===============>|Filter: ((unique2 = $1) OR ... |
                    | |(cost=0.00...         | |                    |  |  |         ---------------------------------
                    | ------------------------ |                    |  |  |
                    ----------------------------                    |  |  |
------------------- InitPlan 2 -------------------                  |  |  |
| ----------------------         --------------- |                  |  |  |
| |Seq Scan on int4_tbl|         |Aggregate    | |                  |  |  |
| |                    |-------> |             |-|------------------|  |  |
| |(cost=0.00...       |         |(cost=0.00...| |                     |  |
| ----------------------         --------------- |                     |  |
--------------------------------------------------                     |  |
                    ------------ SubPlan 3 -------------               |  |
                    | -------------------------------- |               |  |
                    | |Index Scan using tenk1_unique1| |               |  |
                    | |on tenk1 z                    | |               |  |
                    | |Index Cond: (unique1 = $2)    |-|----------------  |
                    | |(cost=0.00...                 | |                  |
                    | -------------------------------- |                  |
                    ------------------------------------
                    ------ SubPlan 4 --------
                    | --------------------- |                             |
                    | |Seq Scan on tenk1 z| |                             |
                    | |(cost=0.00...      |-|-----------------------------|
                    | --------------------- |
                    -------------------------
___________________________________________________________________________________________________________________________

NOTE:
* In proposed explain figure, new SubPlan shapes are colored violet.

--

Thanks & Regards,
Ashesh Vashi

EnterpriseDB INDIA: http://www.enterprisedb.com