Re: EXPLAIN WITH

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> I definitely feel that it would be best to make this change now, when
> we're introducing CTE as a type that anything doing EXPLAIN would need
> to deal with at some level anyway than to add it later (eg: 8.5). I'm
> definitely a +1 on this.

Here's a somewhat contrived example of what you get now:

regression=# explain 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);
QUERY PLAN
-------------------------------------------------------------------------------------
CTE Scan on wumpus (cost=459.07..83371.07 rows=5025 width=244)
Filter: ((unique2 = $1) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
CTE wumpus
-> Seq Scan on tenk1 (cost=0.00..458.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 4
-> Seq Scan on tenk1 z (cost=0.00..458.00 rows=10000 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)
(12 rows)

Before, it would have been pretty tough to tell which subplan
did what.

The ruleutils printout of a subplan reference still leaves some things
to be desired, eg in this example you can't really tell it's an EXISTS
check as opposed to something else. But fixing that is probably too
much for this late date --- I'm not even real clear on what a better
printout would look like, exactly.

regards, tom lane