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 <[email protected] > 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
