Re: EXPLAIN WITH

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: EXPLAIN WITH
Date: 2009-04-05 14:38:40
Message-ID: 20090405143840.GX8123@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Sun, Apr 5, 2009 at 9:40 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > I'm a bit unsatisfied with this output because it doesn't tell me the
> > plan it used to construct the CTE being scanned.
>
> I'm totally wrong. Sorry for the noise.

Eh. It could be made clearer what's happening, imv. Esp. if you have a
number of WITH pieces and want to know which ones connect to which in
the plan between the CTE nodes and the plans that they're running...

eg:

postgres=# explain with wumpus as (select * from foo where id < 200), abc as (select * from tab1)
select * from foo f, wumpus c, wumpus u, abc x where f.creator_id = c.id and
f.last_updater_id = u.id;
QUERY PLAN
------------------------------------------------------------------------------------------
Nested Loop (cost=1266.59..1001458.19 rows=35936310 width=52)
InitPlan
-> Seq Scan on foo (cost=0.00..34.25 rows=647 width=12)
Filter: (id < 200)
-> Seq Scan on tab1 (cost=0.00..27.70 rows=1770 width=16)
-> CTE Scan on abc x (cost=0.00..35.40 rows=1770 width=16)
-> Materialize (cost=1204.64..1566.67 rows=20303 width=36)
-> Hash Join (cost=42.05..1025.34 rows=20303 width=36)
Hash Cond: (f.creator_id = c.id)
-> Hash Join (cost=21.03..275.66 rows=6276 width=24)
Hash Cond: (f.last_updater_id = u.id)
-> Seq Scan on foo f (cost=0.00..29.40 rows=1940 width=12)
-> Hash (cost=12.94..12.94 rows=647 width=12)
-> CTE Scan on wumpus u (cost=0.00..12.94 rows=647 width=12)
-> Hash (cost=12.94..12.94 rows=647 width=12)
-> CTE Scan on wumpus c (cost=0.00..12.94 rows=647 width=12)
(16 rows)

I see the initplan's for foo and tab1, and can figure out where those
fit in, but in a more complex situation it would be much less clear, I
believe. Would be nice if there was a CTE ID or similar to link between
the pieces of the InitPlan and the CTE nodes.

I think that makes sense anyway, I havn't played with CTE much but
that's what it looks like to me.

Stephen

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2009-04-05 14:50:01 Re: EXPLAIN WITH
Previous Message Robert Haas 2009-04-05 14:20:06 Re: EXPLAIN WITH