Horton

Thread

Commits

  1. Improve ruleutils' printout of LATERAL references within subplans.

  2. Teach EXPLAIN to print PARAM_EXEC Params as the referenced expressions,

  1. About displaying NestLoopParam

    Richard Guo <guofenglinux@gmail.com> — 2022-09-16T09:59:11Z

    I have a question about displaying NestLoopParam. In the plan below,

# explain (costs off)
select * from a, lateral (select sum(i) as i from b where exists (select
sum(c.i) from c where c.j = a.j and c.i = b.i) ) ss where a.i = ss.i;
                             QUERY PLAN
--------------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on a
   ->  Subquery Scan on ss
         Filter: (a.i = ss.i)
         ->  Aggregate
               ->  Seq Scan on b
                     Filter: (SubPlan 1)
                     SubPlan 1
                       ->  Aggregate
                             ->  Seq Scan on c
                                   Filter: ((j = $0) AND (i = b.i))
(11 rows)

There are three Params. Param 0 (a.j) and param 2 (a.i) are from
nestParams of the NestLoop. Param 1 (b.i) is from parParam of the
SubPlan. As we can see, param 1 and param 2 are displayed as the
corresponding expressions, while param 0 is displayed as $0.

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.
     */

My question is why is that?

Thanks
Richard

  2. Re: About displaying NestLoopParam

    Richard Guo <guofenglinux@gmail.com> — 2022-09-20T08:55:11Z

    On Fri, Sep 16, 2022 at 5:59 PM Richard Guo <guofenglinux@gmail.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

  3. Re: About displaying NestLoopParam

    Greg Stark <stark@mit.edu> — 2022-11-16T21:13:27Z

    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@sss.pgh.pa.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@gmail.com> wrote:
>
>
> On Fri, Sep 16, 2022 at 5:59 PM Richard Guo <guofenglinux@gmail.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

  4. Re: About displaying NestLoopParam

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-17T00:22:27Z

    Greg Stark <stark@mit.edu> writes:
> I do note that the code in question was added in this commit in 2010.
> That predates the addition of LATERAL in 2013.

Yeah.  It's pretty clear from the comments that I was concerned about
false matches of PARAM_EXEC numbers.  I think that was a live issue
at the time but is so no longer, cf. 46c508fbc and 1db5667ba.
The possibility of LATERAL references is what makes it interesting
to search higher in the plan tree, so there wasn't any real reason to
take any risk of a false match.

I think I might've also been concerned about printing misleading
names for any Vars we did find, due to them belonging to a different
query level.  That's probably a dead issue too now that ruleutils
assigns unique aliases to all RTEs in the query (I'm not sure if
it did at the time).

Looking at this now, it seems a little weird to me that we allow
LATERAL values to be passed down directly into the subplan rather
than having them go through the parParam mechanism.  (If they did,
ruleutils' restriction would be fine.)  I don't know of a reason
to change that, though.

> 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.

No, because that $0 is for a subplan/initplan output, which we don't
have any other sort of name for.  Your example produces output that
explains what it is:

   InitPlan 1 (returns $0)
     ...
               Filter: ((y.j + 1) = $0)

although I'm not sure that we document that anywhere user-facing.

> 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.

I'm content to apply this (although I quibble with removal of some
of the commentary).  Worst case, somebody will find an example where
it produces wrong/misleading output, and we can revert it.  But
the regression test changes show that it does produce useful output
in at least some cases.

			regards, tom lane