Re: improve EXPLAIN for wide tables

Sami Imseih <samimseih(at)gmail(dot)com> writes:
>> It only takes one case to mean we have to deal with it ;-). But I'm
>> fairly sure that there are many other cases, since the parser doesn't
>> restrict the output names of a sub-SELECT to be unique.

> good point. I see the error in my original line of thinking now.
> In fact, it's this simple to prove that we still need to unique-ify
> something like this subquery is valid:

> select * from (select 1 a, 2 a) as s

Actually, I was expecting you to cite that as a counterexample ;-)
because EXPLAIN doesn't show the sub-select's column names in
such cases:

=# explain verbose select * from (select 1 a, 2 a) as s;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: 1, 2
(2 rows)

You need something where we don't elide the SubqueryScan node
to show there's an issue, for example:

=# explain verbose select * from (select 1 a, 2 b, 3 b limit 4) as s where a < 3;
QUERY PLAN
-------------------------------------------------------
Subquery Scan on s (cost=0.00..0.02 rows=1 width=12)
Output: s.a, s.b, s.b_1
Filter: (s.a < 3)
-> Limit (cost=0.00..0.01 rows=1 width=12)
Output: 1, 2, 3
-> Result (cost=0.00..0.01 rows=1 width=12)
Output: 1, 2, 3
(7 rows)

> I suspect that we can also skip RTE_RELATION, since columns must
> be unique, but I am not sure it's worth the extra effort. At least my test
> does not show any real benefit.

Yeah, I was thinking of that too. Seems like it ought to be
a noticeable improvement if the join case is.

> I am attaching a patch that deals with the RTE_JOIN case.

I'll take a look. Thanks for the test demonstrating that
this makes a visible performance difference.

regards, tom lane