Message-ID: <b2f522c6-c150-4f1f-a722-f70e9a75fe13@postgrespro.ru>
Date: Wed, 11 Oct 2023 10:51:26 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Removing unneeded self joins
To: Alena Rybakina <lena.ribackina@yandex.ru>
Cc: "Gregory Stark (as CFM)" <stark.cfm@gmail.com>,
 =?UTF-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Alexander Korotkov <aekorotkov@gmail.com>
References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru>
 <CALNJ-vS4PtC0e53=8fTEe8t3U5d4cTOJYL1PAQsn4=aBk9C5tQ@mail.gmail.com>
 <CALNJ-vTyL-LpvSOPZxpC63Et3LJLUAFZSfRqGEhT5Rj7_EEj7w@mail.gmail.com>
 <0fd15c10-eaa5-51bd-b5c7-e635fe03a94d@postgrespro.ru>
 <d9493d5a-b950-465e-f09b-62c92a23fba1@postgrespro.ru>
 <20221206184643.zya44h5auv6f45zp@awork3.anarazel.de>
 <36579124-f9d9-a51e-38b3-af3511921c7e@postgrespro.ru>
 <88079CBD-B5BB-4C61-A68B-A8C67599E1C7@kleczek.org>
 <CAM-w4HMZXW+hKc8C+WLQ+gdpEkoh9378a2UGKyZRz7mZVerG+Q@mail.gmail.com>
 <c97574c4-13e6-9107-26ab-af2bd626fc85@postgrespro.ru>
 <bcf2f3c5-fbdc-f85b-af3e-ebec616160f6@postgrespro.ru>
 <05469e80-454b-41d7-d04f-421f42bbd4a9@postgrespro.ru>
 <CAPpHfdt9G3EU+vXuqexhweEQoMF-acPUUFuB6hV2Kg0gCy7AjA@mail.gmail.com>
 <ebe66358-428c-439f-85dd-da72c9b2b0c2@postgrespro.ru>
 <CAPpHfdt-0kVV7O==aJEbjY2iGYBu+XBzTHEbPv_6sVNeC7fffQ@mail.gmail.com>
 <48c11b23-9c23-440e-9a3c-020e05196449@postgrespro.ru>
 <96f66ae3-df10-4060-9844-4c9633062cd3@yandex.ru>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <96f66ae3-df10-4060-9844-4c9633062cd3@yandex.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b2f522c6-c150-4f1f-a722-f70e9a75fe13%40postgrespro.ru>
Precedence: bulk

On 11/10/2023 02:29, Alena Rybakina wrote:
> I have reviewed your patch and I noticed a few things.

Thanks for your efforts,

> I have looked at the latest version of the code, I assume that the error 
> lies in the replace_varno_walker function, especially in the place where 
> we check the node by type Var, and does not form any NullTest node.

It's not a bug, it's an optimization we discussed with Alexander above.

> Secondly, I added some code in some places to catch erroneous cases and 
> added a condition when we should not try to apply the self-join-removal 
> transformation due to the absence of an empty self-join list after 
> searching for it and in general if there are no joins in the query. 
> Besides, I added a query for testing and wrote about it above. I have 
> attached my diff file.
Ok, I will look at this
> In addition, I found a comment for myself that was not clear to me. I 
> would be glad if you could explain it to me.
> 
> You mentioned superior outer join in the comment, unfortunately, I 
> didn't find anything about it in the PostgreSQL code, and this 
> explanation remained unclear to me. Could you explain in more detail 
> what you meant?
I meant here that only clauses pushed by reconsider_outer_join_clauses() 
can be found in the joininfo list, and they are not relevant, as you can 
understand.
Having written that, I realized that it was a false statement. ;) - 
joininfo can also contain results of previous SJE iterations, look:

CREATE TABLE test (oid int PRIMARY KEY);
CREATE UNIQUE INDEX ON test((oid*oid));
explain
SELECT count(*)
FROM test c1, test c2, test c3
WHERE c1.oid=c2.oid AND c1.oid*c2.oid=c3.oid*c3.oid;
explain
SELECT count(*)
FROM test c1, test c2, test c3
WHERE c1.oid=c3.oid AND c1.oid*c3.oid=c2.oid*c2.oid;
explain
SELECT count(*)
FROM test c1, test c2, test c3
WHERE c3.oid=c2.oid AND c3.oid*c2.oid=c1.oid*c1.oid;

Having executed this SQL code, you could see that in the last query, the 
SJE feature didn't delete one of the JOINs because of the reason I had 
written above.
It's not an one-minute fix - I will try to propose solution a bit later.

-- 
regards,
Andrey Lepikhov
Postgres Professional