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

Re: EXPLAIN format changes

From: Dave Page <dpage(at)pgadmin(dot)org>
To: Ashesh Vashi <ashesh(dot)vashi(at)enterprisedb(dot)com>
Cc: pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org>
Subject: Re: EXPLAIN format changes
Date: 2009-04-06 11:43:31
Message-ID: 937d27e10904060443w58ac350am753e5b6571627e62@mail.gmail.com (view raw or flat)
Thread:
Lists: pgadmin-hackers
Adding -hackers for additional input. Apologies for the rich text email -
Ashesh's drawing needed it!!

On Mon, Apr 6, 2009 at 12:34 PM, Ashesh Vashi <ashesh(dot)vashi(at)enterprisedb(dot)com
> wrote:

>  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. :(
>

Urgh.


>
>
> 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.
>

I'm inclined to agree that re-hashing the artwork is a no-go for 1.10.
Instead of removing the subplan text, can we ensure it is present on all
appropriate nodes?


>
>
> 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
>



-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Responses

pgadmin-hackers by date

Next:From: Ashesh VashiDate: 2009-04-06 13:17:25
Subject: Re: EXPLAIN format changes
Previous:From: Dave PageDate: 2009-04-06 10:30:34
Subject: Re: SVN Commit by dpage: r7798 - in trunk/pgadmin3/pgadmin: dlg schema

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