Re: EXPLAIN format changes

Поиск
Список
Период
Сортировка
От Ashesh Vashi
Тема Re: EXPLAIN format changes
Дата
Msg-id 49DAE990.80100@enterprisedb.com
обсуждение исходный текст
Ответ на Re: EXPLAIN format changes  (Dave Page <dpage@pgadmin.org>)
Ответы Re: EXPLAIN format changes  (Dave Page <dpage@pgadmin.org>)
Список pgadmin-hackers
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: svn@pgadmin.org
Дата:
Сообщение: SVN Commit by dpage: r7799 - trunk/pgadmin3/pgadmin/debugger
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: Debugger hung on dummy function