Re: BUG #18187: Unexpected error: "variable not found in subplan target lists" triggered by JOIN

On 28/11/2023 15:03, Richard Guo wrote:
> On Tue, Nov 28, 2023 at 3:03 PM Richard Guo <guofenglinux(at)gmail(dot)com
> <mailto:guofenglinux(at)gmail(dot)com>> wrote:
> On Tue, Nov 28, 2023 at 1:42 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
> BTW, why is it that it seems to prefer to remove the first of
> the two self-joined rels, rather than the second?  That seems
> jarringly bizarre.
> Hmm, I'm not sure either.  Alexander and Andrei, could you please share
> your insights?

I was thinking about that choice and, in earlier versions, removed outer
relations. I did not find a difference in removing a specific side of
the JOIN. Rewriting the patch right before the commit, I found out that
remove_useless_joins removed the inner join and chose to do the same,
like a convention.

> BTW, while reading the codes, I noticed this commit of
> remove_self_joins_recurse.
>
> * ... To avoid complexity, limit the max power of this set by a GUC.
>
> But where is the GUC?   I guess that it refers to
> self_join_search_limit, which has been removed during development.
>
> So we should revise this commit to at least remove any mention of the
> GUC.  Maybe it'd better to add a new commit explaining why we are not
> concerned about cases where the number of self joins is too large.

Maybe. But actually, we just did some benchmarking to see the influence.
Moreover, Alexander's code on preliminary sorting the relations and the
fact that inheritance isn't still expanded were reasons we aren't afraid
of significant degradation in practical cases.

--
regards,
Andrei Lepikhov
Postgres Professional