Обсуждение: About displaying NestLoopParam
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
# 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
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
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
Вложения
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
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