MIME-Version: 1.0
References: <CAMbWs48RKiZGFEd5A0JtztRY5ZdvVvNiHh0AKeuoz21F+0dVjQ@mail.gmail.com>
In-Reply-To: <CAMbWs48RKiZGFEd5A0JtztRY5ZdvVvNiHh0AKeuoz21F+0dVjQ@mail.gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 25 Apr 2024 15:20:10 +0800
Message-ID: <CAMbWs4-5aehbCAJLqE-xqFRcJK3s-y3EX73YbTrjDQexcLBUhg@mail.gmail.com>
Subject: Re: Short-circuit sort_inner_and_outer if there are no mergejoin clauses
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000be16e70616e69e3e"
Archived-At: <https://www.postgresql.org/message-id/CAMbWs4-5aehbCAJLqE-xqFRcJK3s-y3EX73YbTrjDQexcLBUhg%40mail.gmail.com>
Precedence: bulk

--000000000000be16e70616e69e3e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 24, 2024 at 5:13=E2=80=AFPM Richard Guo <guofenglinux@gmail.com=
> wrote:

> In sort_inner_and_outer, we create mergejoin join paths by explicitly
> sorting both relations on each possible ordering of the available
> mergejoin clauses.  However, if there are no available mergejoin
> clauses, we can skip this process entirely.  It seems that this is a
> relatively common scenario.  Checking the regression tests I noticed
> that there are a lot of cases where we would arrive here with an empty
> mergeclause_list.
>

FWIW, during the run of the core regression tests, I found that we enter
sort_inner_and_outer with an empty mergeclause_list a total of 11064
times.  Out of these occurrences, there are 293 instances where the join
type is JOIN_UNIQUE_OUTER, indicating the need to create a UniquePath
for the outer path.  Similarly, there are also 293 instances where the
join type is JOIN_UNIQUE_INNER, indicating the need to create a
UniquePath for the inner path.

Thanks
Richard

--000000000000be16e70616e69e3e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Wed, Apr 24, 2024 at 5:13=E2=80=AFPM R=
ichard Guo &lt;<a href=3D"mailto:guofenglinux@gmail.com">guofenglinux@gmail=
.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><div dir=3D"ltr">In sort_inner_and_outer, we create mergejoin join path=
s by explicitly<br>sorting both relations on each possible ordering of the =
available<br>mergejoin clauses.=C2=A0 However, if there are no available me=
rgejoin<br>clauses, we can skip this process entirely.=C2=A0 It seems that =
this is a<br>relatively common scenario.=C2=A0 Checking the regression test=
s I noticed<br>that there are a lot of cases where we would arrive here wit=
h an empty<br>mergeclause_list.</div></blockquote><div><br></div><div>FWIW,=
 during the run of the core regression tests, I found that we enter<br>sort=
_inner_and_outer with an empty mergeclause_list a total of 11064<br>times.=
=C2=A0 Out of these occurrences, there are 293 instances where the join<br>=
type is JOIN_UNIQUE_OUTER, indicating the need to create a UniquePath<br>fo=
r the outer path.=C2=A0 Similarly, there are also 293 instances where the<b=
r>join type is JOIN_UNIQUE_INNER, indicating the need to create a<br>Unique=
Path for the inner path.<br><br>Thanks<br>Richard<br></div></div></div>

--000000000000be16e70616e69e3e--