Re: EXPLAIN WITH

* 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