pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Gregory Stark (as CFM) <stark.cfm@gmail.com>
Cc: Michał Kłeczek <michal@kleczek.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Removing unneeded self joins
Date: Mon, 23 Oct 2023 10:43:13 +0700
Message-ID: <922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru> (raw )
In-Reply-To: <CAPpHfdsz8E085YF2VoJe2+h4YAShsh7p--t3kQfQTntiCy63Xg@mail.gmail.com >
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 >
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
Attachments:
minor_changes.diff (text/plain, 685B) download
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 6b848aadad..b84197dadb 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -23,7 +23,6 @@
#include "postgres.h"
#include "catalog/pg_class.h"
-#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/joininfo.h"
@@ -50,7 +49,7 @@ typedef struct UniqueRelInfo
} UniqueRelInfo;
/*
- * The context for replace_varno_walker() containing source and target relids2
+ * The context for replace_varno_walker() containing source and target relids.
*/
typedef struct
{
view thread (247+ messages) latest in thread
Message-ID: <922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru>
Permalink: ../922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru/
Also on: postgresql.org/message-id/922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru
copy link · copy postgr.es · copy git am
reply 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-hackers@postgresql.org
Cc: a.lepikhov@postgrespro.ru, aekorotkov@gmail.com, stark.cfm@gmail.com, michal@kleczek.org, pgsql-hackers@lists.postgresql.org
Subject: Re: Removing unneeded self joins
In-Reply-To: <922623af-872e-4cdc-9238-129b18bee42d@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