Re: Confusing EXPLAIN output in case of inherited tables

I wrote:
> I got interested in this problem again now that we have a user complaint
> about it (bug #7553).
> ...
> As far as the relation-rename problem goes, I propose that what we
> should do is have ruleutils.c invent nonconflicting fake aliases for
> each RTE in the query tree. This would allow getting rid of some of the
> dubious heuristics in get_variable: it should just print the chosen
> alias and be done. (It still has to do something different for unnamed
> joins, but we can leave that part alone I think.)

Attached is a draft patch for this. It fixes the view-dumping problems
that I exhibited in
http://archives.postgresql.org/message-id/29791.1327718297@sss.pgh.pa.us
as well as nicely cleaning up Ashutosh's original complaint at
http://archives.postgresql.org/pgsql-hackers/2012-01/msg00505.php
There are quite a few more changes in the regression-test plan printouts
than was originally discussed, but they seem to be generally for the
better IMO: for instance there is no longer any problem with different
RTEs being printed with identical names in EXPLAIN.

One thing I found while working on this is that some of the
inconsistency is not really EXPLAIN's fault but the planner's: the
planner does not take any trouble to avoid duplicate RTE aliases when it
manufactures additional RTEs, which it does in at least two places
(inheritance expansion and min/max aggregate optimization). In my first
version of the patch I was getting EXPLAIN printouts like this for
inheritance append-plans:

Nested Loop
-> Limit
-> Seq Scan on int4_tbl
-> Append
! -> Index Scan using patest0i on patest0 patest0_1
Index Cond: (id = int4_tbl.f1)
! -> Index Scan using patest1i on patest1
Index Cond: (id = int4_tbl.f1)
! -> Index Scan using patest2i on patest2
Index Cond: (id = int4_tbl.f1)

That happened because the original inheritance-root RTE got the
"patest0" alias, and then the inheritance-child RTE for the parent
relation got stuck with "patest0_1". This isn't terribly desirable
since the inheritance-root RTE isn't actually visible anywhere in
the EXPLAIN printout, so giving it the preferred name isn't ideal.

In the attached I've hacked around this by causing the planner to
assign new aliases to RTEs that it replaces in this way (see planagg.c
and prepunion.c diffs). This seems like a bit of a kluge, but it
doesn't take much code. An alternative that I'm considering is to
have EXPLAIN make a pre-pass over the plan tree to identify which
RTEs will actually be referenced, and then consider only those RTEs
while assigning aliases. This would be a great deal more code though,
and code which would require maintenance every time we add plan node
types etc. So I'm not sure it's really a better answer. Thoughts?

regards, tom lane