Re: Improving EXPLAIN's display of SubPlan nodes

Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> writes:
> On Sat, 16 Mar 2024 at 17:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> After looking at your draft some more, it occurred to me that we're
>> not that far from getting rid of showing "$n" entirely in this
>> context. Instead of (subplan_name).$n, we could write something like
>> (subplan_name).colN or (subplan_name).columnN or (subplan_name).fN,

> I think it would be confusing if there were tables whose columns are
> named "colN". In that case, a SQL qual like "t1.col2 = t2.col2" might
> be output as something like "t1.col2 = (SubPlan 1).col3", since the
> subplan's targetlist wouldn't necessarily just output the table
> columns in order.

Perhaps. I think people who are using columns named like that are
already accustomed to having to pay close attention to which table
the column is shown as qualified by. So I'm not sure there'd really
be much problem in practice.

> I actually think "$n" is not so bad (especially if we make n the
> column number). The fact that it's qualified by the subplan name ought
> to be sufficient to avoid it being confused with an external
> parameter.

That's an interesting compromise position, but I'm not sure that
it buys much compared to the approach shown in your draft (that
is, continuing to use the real param IDs). The real IDs at least
have the advantage of being unique.

> Whatever name is chosen, I think we should still output "(returns
> ...)" on the subplan nodes.

We should do that if we continue to show real param IDs, but
if we change to using column numbers then I think it's pointless.
Output like

SubPlan 1 (returns $1,$2)
...
SubPlan 2 (returns $1,$2,$3)

seems to me that it'd be more confusing not less so. Does SubPlan 2
return the same values as SubPlan 1 plus more?

> Overall, I think this is heading in the right direction. I think we
> just need a good way to say "the n'th output column of the subplan",
> that can't be confused with anything else in the output.

We could consider notations like "(SubPlan 1 column 2)", which
couldn't be confused with anything else, if only because a name
like that would have to be double-quoted. It's a little more
verbose but not that much. I fear "(SubPlan 1 col 2)" is too
short to be clear.

regards, tom lane