Re: EXPLAIN WITH - Mailing list pgsql-hackers

I wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> Erm, of course, the CTE *has* an ID already, since you name them.  Could
>> we get that ID/name up into the piece of the InitPlan that is handling
>> that CTE?

> I'm not sure but will be glad to take a look.  Assuming it's not
> unreasonably difficult, does anyone object to a format like this:

I looked at this a bit and found that it *is* unreasonably difficult
given what's currently stored in plan trees.  SubPlans and InitPlans
are identified only by ID numbers.  In principle we could search the
plan tree for a CTE Scan node referencing that ID number and then
grab the name of the RTE it refers to ... but ick, especially
considering we'd have to do that for every subplan, even the ones
that are not CTEs.

What we could do instead, which is a pretty simple change, is to
add a "name" field to struct SubPlan.  If we were going to do this,
I'd vote for filling it in for every subplan.  For actual CTEs we
could fill in "CTE name"; for anonymous subqueries we could do
no better than "InitPlan n" or "SubPlan n".  However, that would
still be a step forward, because then we could have references to
subplans say "subplan n" instead of just "subplan".  So instead
of 

regression=# explain select * from tenk1 a where unique2 not in (select f1 from int4_tbl);
QUERYPLAN                           
 
----------------------------------------------------------------Seq Scan on tenk1 a  (cost=1.06..484.06 rows=5000
width=244) Filter: (NOT (hashed subplan))  SubPlan    ->  Seq Scan on int4_tbl  (cost=0.00..1.05 rows=5 width=4)
 
(4 rows)

you'd get
Seq Scan on tenk1 a  (cost=1.06..484.06 rows=5000 width=244)  Filter: (NOT (hashed subplan 1))  SubPlan 1    ->  Seq
Scanon int4_tbl  (cost=0.00..1.05 rows=5 width=4)
 

This isn't terribly compelling in this example, of course, but
it gets a lot more important when you've got a dozen of 'em.

From the perspective of the backend this is a simple and cheap change.
I think the objection that is most likely to be raised is that it would
confuse or break programs that analyze EXPLAIN output in any degree of
detail.  Of course such programs are going to need some work for 8.4
already.

Comments?
        regards, tom lane