Re: EXPLAIN WITH

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Sun, Apr 5, 2009 at 9:40 AM, Robert Haas <robertmhaas@gmail.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.19rows=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.40rows=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.66rows=6276 width=24)                    Hash Cond: (f.last_updater_id = u.id)                    ->
SeqScan 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.94rows=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