Message-ID: <17673e5f-6f68-4be7-a4f9-608f5bb58e9a@postgrespro.ru>
Date: Tue, 28 Nov 2023 15:24:51 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18187: Unexpected error: "variable not found in subplan
 target lists" triggered by JOIN
To: Richard Guo <guofenglinux@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Korotkov <aekorotkov@gmail.com>, zuming.jiang@inf.ethz.ch,
 pgsql-bugs@lists.postgresql.org,
 PG Bug reporting form <noreply@postgresql.org>
References: <18187-831da249cbd2ff8e@postgresql.org>
 <491881ef-f1c7-4a86-b5dd-dcad55a88ab4@postgrespro.ru>
 <CAMbWs4_yrqMYXd+zXc4Y6KjnyA8xPZE6aiAW6k2Y10WaNhwRhw@mail.gmail.com>
 <CAMbWs4-ns73VF9gi37q61G3dS6Xuos+HtryMaBh37WQn=BsaJw@mail.gmail.com>
 <CAMbWs4_WeARU0oYH1sMn03VYzw-qZQ_xwUNq7r6TbDTH+VgXng@mail.gmail.com>
 <3883031.1701106936@sss.pgh.pa.us>
 <CAMbWs48UBgxyzNKgu7ZrWDGy-6y33+uC8znyDceRzaDb=+XOcw@mail.gmail.com>
 <CAMbWs48zG6q=AK2k4hGgj64FG30iBPe5WPyu9rumtmqBOWDZfA@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAMbWs48zG6q=AK2k4hGgj64FG30iBPe5WPyu9rumtmqBOWDZfA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/17673e5f-6f68-4be7-a4f9-608f5bb58e9a%40postgrespro.ru>
Precedence: bulk

On 28/11/2023 15:03, Richard Guo wrote:
> On Tue, Nov 28, 2023 at 3:03 PM Richard Guo <guofenglinux@gmail.com 
> <mailto:guofenglinux@gmail.com>> wrote:
>     On Tue, Nov 28, 2023 at 1:42 AM Tom Lane <tgl@sss.pgh.pa.us
>     <mailto:tgl@sss.pgh.pa.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