Content-Type: multipart/mixed; boundary="------------lW6IStaFkEldtZ8k9Wgv4Owu"
Message-ID: <922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru>
Date: Mon, 23 Oct 2023 10:43:13 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Removing unneeded self joins
To: Alexander Korotkov <aekorotkov@gmail.com>
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>
References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru>
 <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>
 <CAPpHfdsQkY0Gu7bQ9Ekdyzo_5Xxz74TYKbwmk1on1WAASpxXcQ@mail.gmail.com>
 <8800ccf2-ae82-461c-92a1-d4c0a22f8cf2@postgrespro.ru>
 <CAPpHfdvJ3jBQaJQKrf69K++kXi+Tvzn6TXO-DKXj0KVJjvMebg@mail.gmail.com>
 <1bd80083-a13e-4e9f-a7ce-73c3faab521c@postgrespro.ru>
 <CAPpHfdsz8E085YF2VoJe2+h4YAShsh7p--t3kQfQTntiCy63Xg@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAPpHfdsz8E085YF2VoJe2+h4YAShsh7p--t3kQfQTntiCy63Xg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/922623af-872e-4cdc-9238-129b18bee42d%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------lW6IStaFkEldtZ8k9Wgv4Owu
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 22/10/2023 05:01, Alexander Korotkov wrote:
> On Thu, Oct 19, 2023 at 6:16 AM Andrei Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> On 19/10/2023 01:50, Alexander Korotkov wrote:
>>> This query took 3778.432 ms with self-join removal disabled, and
>>> 3756.009 ms with self-join removal enabled.  So, no measurable
>>> overhead.  Similar to the higher number of joins.  Can you imagine
>>> some extreme case when self-join removal could introduce significant
>>> overhead in comparison with other optimizer parts?  If not, should we
>>> remove self_join_search_limit GUC?
>> Thanks,
>> It was Zhihong Yu who worried about that case [1]. And my purpose was to
>> show a method to avoid such a problem if it would be needed.
>> I guess the main idea here is that we have a lot of self-joins, but only
>> few of them (or no one) can be removed.
>> I can't imagine a practical situation when we can be stuck in the
>> problems here. So, I vote to remove this GUC.
> 
> I've removed the self_join_search_limit.  Anyway there is
> enable_self_join_removal if the self join removal algorithm causes any
> problems.  I also did some grammar corrections for the comments.  I
> think the patch is getting to the committable shape.  I noticed some
> failures on commitfest.cputube.org.  I'd like to check how this
> version will pass it.

I have observed the final patch. A couple of minor changes can be made 
(see attachment).
Also, I see room for improvement, but it can be done later. For example, 
we limit the optimization to only ordinary tables in this patch. It can 
be extended at least with partitioned and foreign tables soon.

-- 
regards,
Andrey Lepikhov
Postgres Professional

--------------lW6IStaFkEldtZ8k9Wgv4Owu
Content-Type: text/plain; charset=UTF-8; name="minor_changes.diff"
Content-Disposition: attachment; filename="minor_changes.diff"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL2FuYWx5emVqb2lucy5j
IGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vYW5hbHl6ZWpvaW5zLmMKaW5kZXggNmI4
NDhhYWRhZC4uYjg0MTk3ZGFkYiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvb3B0aW1pemVy
L3BsYW4vYW5hbHl6ZWpvaW5zLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4v
YW5hbHl6ZWpvaW5zLmMKQEAgLTIzLDcgKzIzLDYgQEAKICNpbmNsdWRlICJwb3N0Z3Jlcy5o
IgogCiAjaW5jbHVkZSAiY2F0YWxvZy9wZ19jbGFzcy5oIgotI2luY2x1ZGUgIm5vZGVzL21h
a2VmdW5jcy5oIgogI2luY2x1ZGUgIm5vZGVzL25vZGVGdW5jcy5oIgogI2luY2x1ZGUgIm9w
dGltaXplci9jbGF1c2VzLmgiCiAjaW5jbHVkZSAib3B0aW1pemVyL2pvaW5pbmZvLmgiCkBA
IC01MCw3ICs0OSw3IEBAIHR5cGVkZWYgc3RydWN0IFVuaXF1ZVJlbEluZm8KIH0gVW5pcXVl
UmVsSW5mbzsKIAogLyoKLSAqIFRoZSBjb250ZXh0IGZvciByZXBsYWNlX3Zhcm5vX3dhbGtl
cigpIGNvbnRhaW5pbmcgc291cmNlIGFuZCB0YXJnZXQgcmVsaWRzMgorICogVGhlIGNvbnRl
eHQgZm9yIHJlcGxhY2VfdmFybm9fd2Fsa2VyKCkgY29udGFpbmluZyBzb3VyY2UgYW5kIHRh
cmdldCByZWxpZHMuCiAgKi8KIHR5cGVkZWYgc3RydWN0CiB7Cg==

--------------lW6IStaFkEldtZ8k9Wgv4Owu--