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

Re: EXPLAIN format changes

From: Ashesh Vashi <ashesh(dot)vashi(at)enterprisedb(dot)com>
To: Dave Page <dpage(at)pgadmin(dot)org>
Cc: pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org>
Subject: Re: EXPLAIN format changes
Date: 2009-04-07 05:50:08
Message-ID: 49DAE990.80100@enterprisedb.com (view raw or flat)
Thread:
Lists: pgadmin-hackers
Hi Team,

Dave Page wrote:
> On Mon, Apr 6, 2009 at 2:17 PM, Ashesh Vashi
> <ashesh(dot)vashi(at)enterprisedb(dot)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

In response to

Responses

pgadmin-hackers by date

Next:From: Heikki LinnakangasDate: 2009-04-07 07:45:27
Subject: Re: Debugger hung on dummy function
Previous:From: Paragon CorporationDate: 2009-04-07 05:45:44
Subject: PgAdmin III 1.10 1 beta can't create pgagent batch job steps

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