Re: EXPLAIN format changes

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