Postgres Pro
Downloads
Home   >   mailing lists

Re: EXPLAIN format changes - Mailing list pgadmin-hackers

From Ashesh Vashi
Subject Re: EXPLAIN format changes
Date
Msg-id 49DAE990.80100@enterprisedb.com
Whole thread Raw
In response to Re: EXPLAIN format changes  (Dave Page <dpage@pgadmin.org>)
Responses Re: EXPLAIN format changes
List pgadmin-hackers
Tree view
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

pgadmin-hackers by date:

Previous
From: svn@pgadmin.org
Date:
Subject: SVN Commit by dpage: r7799 - trunk/pgadmin3/pgadmin/debugger
Next
From: Heikki Linnakangas
Date:
Subject: Re: Debugger hung on dummy function