From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Cc: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #18187: Unexpected error: "variable not found in subplan target lists" triggered by JOIN
Date: Tue, 28 Nov 2023 15:24:51 +0700
Message-ID: <17673e5f-6f68-4be7-a4f9-608f5bb58e9a@postgrespro.ru> (raw)
In-Reply-To: <CAMbWs48zG6q=AK2k4hGgj64FG30iBPe5WPyu9rumtmqBOWDZfA@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, guofenglinux@gmail.com, tgl@sss.pgh.pa.us, aekorotkov@gmail.com, noreply@postgresql.org
Subject: Re: BUG #18187: Unexpected error: "variable not found in subplan target lists" triggered by JOIN
In-Reply-To: <17673e5f-6f68-4be7-a4f9-608f5bb58e9a@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox