Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: EXPLAIN format changes

Re: EXPLAIN format changes

From
Dave Page
Date:
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.vashi@enterprisedb.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

Re: EXPLAIN format changes

From
Dave Page
Date:
On Mon, Apr 6, 2009 at 2:17 PM, Ashesh Vashi
<ashesh.vashi@enterprisedb.com> wrote:
> Hi Dave,
>>
>> 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?
>
> SubPlan(s) can represent multiple nodes (steps).
> Do we need to add it to all the nodes or add it to the first node of that
> particular subplan?

All the ones that are part of that plan - if it's possible to do
without too much disruption.

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

Re: EXPLAIN format changes

From
Ashesh Vashi
Date:
Hi Dave,

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?
SubPlan(s) can represent multiple nodes (steps).
Do we need to add it to all the nodes or add it to the first node of that particular subplan?

--
Thanks & Regards,
Ashesh Vashi

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

Re: EXPLAIN format changes

From
Ashesh Vashi
Date:
Hi Team,

Dave Page wrote:
On Mon, Apr 6, 2009 at 2:17 PM, Ashesh Vashi
<ashesh.vashi@enterprisedb.com> wrote:

Hi Dave,

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?

SubPlan(s) can represent multiple nodes (steps).
Do we need to add it to all the nodes or add it to the first node of that
particular subplan?


All the ones that are part of that plan - if it's possible to do
without too much disruption.
I need some help here:

There are couple of questions:
1. How to identify the subplan?

regression=# EXPLAIN WITH abc AS (SELECT * FROM (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) or exists(SELECT 1 from tenk1 b where b.unique1 = wumpus.hundred)) as c) select * FROM abc;
                                                              QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on abc  (cost=166115.40..166265.64 rows=7512 width=244)
   CTE abc
     ->  CTE Scan on wumpus  (cost=446.07..166040.28 rows=7512 width=244)
           Filter: ((unique2 = $1) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6))
           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)
           SubPlan 5
             ->  Index Scan using tenk1_unique1 on tenk1 b  (cost=0.00..8.27 rows=1 width=0)
                   Index Cond: (unique1 = $4)
           SubPlan 6
             ->  Seq Scan on tenk1 b  (cost=0.00..445.00 rows=10000 width=4)
(19 rows)

In above case, "CTE abc" is one of the subplans, how to identify them?
Because we can have "Filter:..." next to "CTE scan on" line, but not always as given in the case.

It is difficult to figure out without hardcoding.
We can assume - if we have "CTE Scan on xxx" then next possible lines are "Filter: ...." (optional) and then "CTE xxx" (subplan - xxx must matche with the first line.)

What do you say?

2. In some case, we can have subplans within subplans, How should we represent them in the nodes part of these plans?

regression=# EXPLAIN WITH abc AS (SELECT * FROM (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) or exists(SELECT 1 from tenk1 b where b.unique1 = wumpus.hundred)) as c) select * from abc where unique2 = (select sum(f1) from int4_tbl) or exists(select 1 from tenk1 v where v.unique1 = abc.thousand);
                                                              QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on abc  (cost=166116.47..228398.16 rows=3775 width=244)
   Filter: ((unique2 = $7) OR (alternatives: SubPlan 9 or hashed SubPlan 10))
   CTE abc
     ->  CTE Scan on wumpus  (cost=446.07..166040.28 rows=7512 width=244)
           Filter: ((unique2 = $1) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6))
           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)
           SubPlan 5
             ->  Index Scan using tenk1_unique1 on tenk1 b  (cost=0.00..8.27 rows=1 width=0)
                   Index Cond: (unique1 = $4)
           SubPlan 6
             ->  Seq Scan on tenk1 b  (cost=0.00..445.00 rows=10000 width=4)
   InitPlan 8 (returns $7)
     ->  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 9
     ->  Index Scan using tenk1_unique1 on tenk1 v  (cost=0.00..8.27 rows=1 width=0)
           Index Cond: (unique1 = $8)
   SubPlan 10
     ->  Seq Scan on tenk1 v  (cost=0.00..445.00 rows=10000 width=4)
(28 rows)

Please give your inputs.

--
Regards,
Ashesh Vashi

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

Re: EXPLAIN format changes

From
Dave Page
Date:
On Tue, Apr 7, 2009 at 6:50 AM, Ashesh Vashi
<ashesh.vashi@enterprisedb.com> wrote:

> There are couple of questions:
> 1. How to identify the subplan?

OK, I think this is going to be too much of a change this far into
beta. We still get a useful diagram as far as I can see, so lets just
leave things as they are for now. In 8.5 we can make a point of
getting XML output from EXPLAIN which will save us from having to
maintain this hideous parser any longer.

Thoughts? Magnus/Guillaume/Hiroshi?

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

Re: EXPLAIN format changes

From
Magnus Hagander
Date:
Dave Page wrote:
> On Tue, Apr 7, 2009 at 6:50 AM, Ashesh Vashi
> <ashesh.vashi@enterprisedb.com> wrote:
>
>> There are couple of questions:
>> 1. How to identify the subplan?
>
> OK, I think this is going to be too much of a change this far into
> beta. We still get a useful diagram as far as I can see, so lets just
> leave things as they are for now. In 8.5 we can make a point of
> getting XML output from EXPLAIN which will save us from having to
> maintain this hideous parser any longer.
>
> Thoughts? Magnus/Guillaume/Hiroshi?

I agree in principle :-)

Haven't looked into the details of what would be required here, but I
agree with both the "let's stay away from too invasive changes" part.
And if we do it now, we'll have to do it over again when we have a more
easily parse:able format in the future.

//Magnus

Re: EXPLAIN format changes

From
Guillaume Lelarge
Date:
Le mardi 07 avril 2009 à 15:18:57, Magnus Hagander a écrit :
> Dave Page wrote:
> > On Tue, Apr 7, 2009 at 6:50 AM, Ashesh Vashi
> >
> > <ashesh.vashi@enterprisedb.com> wrote:
> >> There are couple of questions:
> >> 1. How to identify the subplan?
> >
> > OK, I think this is going to be too much of a change this far into
> > beta. We still get a useful diagram as far as I can see, so lets just
> > leave things as they are for now. In 8.5 we can make a point of
> > getting XML output from EXPLAIN which will save us from having to
> > maintain this hideous parser any longer.
> >
> > Thoughts? Magnus/Guillaume/Hiroshi?
>
> I agree in principle :-)
>
> Haven't looked into the details of what would be required here, but I
> agree with both the "let's stay away from too invasive changes" part.
> And if we do it now, we'll have to do it over again when we have a more
> easily parse:able format in the future.
>

+1 too.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com