Re: About displaying NestLoopParam

So I guess I don't have much to add since I don't really understand
the Param infrastructure, certainly not any better than you seem to.

I do note that the code in question was added in this commit in 2010.
That predates the addition of LATERAL in 2013. I suppose those
comments may be talking about InitPlans for things like constant
subqueries that have been pulled up to InitPlans in queries like:

explain verbose select * from x join y on (x.i=y.j) where y.j+1=(select 5) ;

Which your patch doesn't eliminate the $0 in. I don't know if the code
you're removing is just for efficiency -- to avoid trawling through
nodes of the plan that can't be relevant -- or for correctness.

Fwiw your patch applied for me and built without warnings and seems to
work for all the queries I've thrown at it so far. That's hardly an
exhaustive test of course.

commit 1cc29fe7c60ba643c114979dbe588d3a38005449
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Tue Jul 13 20:57:19 2010 +0000

Teach EXPLAIN to print PARAM_EXEC Params as the referenced expressions,
rather than just $N. This brings the display of nestloop-inner-indexscan
plans back to where it's been, and incidentally improves the display of
SubPlan parameters as well. In passing, simplify the EXPLAIN code by
having it deal primarily in the PlanState tree rather than separately
searching Plan and PlanState trees. This is noticeably cleaner for
subplans, and about a wash elsewhere.

One small difference from previous behavior is that EXPLAIN will no longer
qualify local variable references in inner-indexscan plan nodes, since it
no longer sees such nodes as possibly referencing multiple tables. Vars
referenced through PARAM_EXEC Params are still forcibly qualified, though,
so I don't think the display is any more confusing than before. Adjust a
couple of examples in the documentation to match this behavior.

On Tue, 20 Sept 2022 at 05:00, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
>
> On Fri, Sep 16, 2022 at 5:59 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>>
>> I'm not saying this is a bug, but just curious why param 0 cannot be
>> displayed as the referenced expression. And I find the reason is that in
>> function find_param_referent(), we have the 'in_same_plan_level' flag
>> controlling that if we have emerged from a subplan, i.e. not the same
>> plan level any more, we would not look further for the matching
>> NestLoopParam. Param 0 suits this situation.
>>
>> And there is a comment there also saying,
>>
>> /*
>> * NestLoops transmit params to their inner child only; also, once
>> * we've crawled up out of a subplan, this couldn't possibly be
>> * the right match.
>> */
>
>
> After thinking of this for more time, I still don't see the reason why
> we cannot display NestLoopParam after we've emerged from a subplan.
>
> It seems these params are from parameterized subqueryscan and their
> values are supplied by an upper nestloop. These params should have been
> processed in process_subquery_nestloop_params() that we just add the
> PlannerParamItem entries to root->curOuterParams, in the form of
> NestLoopParam, using the same PARAM_EXEC slots.
>
> So I propose the patch attached to remove the 'in_same_plan_level' flag
> so that we can display NestLoopParam across subplan. Please correct me
> if I'm wrong.
>
> Thanks
> Richard

--
greg