Re: EXPLAIN format changes

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