MIME-Version: 1.0
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 24 Apr 2024 17:13:12 +0800
Message-ID: <CAMbWs48RKiZGFEd5A0JtztRY5ZdvVvNiHh0AKeuoz21F+0dVjQ@mail.gmail.com>
Subject: Short-circuit sort_inner_and_outer if there are no mergejoin clauses
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000002755a80616d415f3"
Archived-At: <https://www.postgresql.org/message-id/CAMbWs48RKiZGFEd5A0JtztRY5ZdvVvNiHh0AKeuoz21F%2B0dVjQ%40mail.gmail.com>
Precedence: bulk

--0000000000002755a80616d415f3
Content-Type: multipart/alternative; boundary="0000000000002755a60616d415f1"

--0000000000002755a60616d415f1
Content-Type: text/plain; charset="UTF-8"

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.

So I'm wondering if it's worth considering a shortcut in
sort_inner_and_outer by checking if mergeclause_list is empty.  This can
help skip all the statements preceding select_outer_pathkeys_for_merge.
In particular this may help avoid building UniquePath paths in the case
of JOIN_UNIQUE_OUTER or JOIN_UNIQUE_INNER.

I asked this because in the "Right Semi Join" patch [1] I wanted to
exclude mergejoin from being considered for JOIN_RIGHT_SEMI.  So I set
mergeclause_list to NIL, but noticed that it still runs the statements
in sort_inner_and_outer until no available outer pathkeys are found in
select_outer_pathkeys_for_merge.

Attached is a trivial patch for this.  Thoughts?

[1]
https://www.postgresql.org/message-id/flat/CAMbWs4_X1mN%3Dic%2BSxcyymUqFx9bB8pqSLTGJ-F%3DMHy4PW3eRXw%40mail.gmail.com

Thanks
Richard

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

<div dir=3D"ltr">In sort_inner_and_outer, we create mergejoin join paths by=
 explicitly<br>sorting both relations on each possible ordering of the avai=
lable<br>mergejoin clauses.=C2=A0 However, if there are no available mergej=
oin<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 tests I =
noticed<br>that there are a lot of cases where we would arrive here with an=
 empty<br>mergeclause_list.<br><br>So I&#39;m wondering if it&#39;s worth c=
onsidering a shortcut in<br>sort_inner_and_outer by checking if mergeclause=
_list is empty.=C2=A0 This can<br>help skip all the statements preceding se=
lect_outer_pathkeys_for_merge.<br>In particular this may help avoid buildin=
g UniquePath paths in the case<br>of JOIN_UNIQUE_OUTER or JOIN_UNIQUE_INNER=
.<br><br>I asked this because in the &quot;Right Semi Join&quot; patch [1] =
I wanted to<br>exclude mergejoin from being considered for JOIN_RIGHT_SEMI.=
=C2=A0 So I set<br>mergeclause_list to NIL, but noticed that it still runs =
the statements<br>in sort_inner_and_outer until no available outer pathkeys=
 are found in<br>select_outer_pathkeys_for_merge.<br><br>Attached is a triv=
ial patch for this.=C2=A0 Thoughts?<div><br></div><div>[1] <a href=3D"https=
://www.postgresql.org/message-id/flat/CAMbWs4_X1mN%3Dic%2BSxcyymUqFx9bB8pqS=
LTGJ-F%3DMHy4PW3eRXw%40mail.gmail.com">https://www.postgresql.org/message-i=
d/flat/CAMbWs4_X1mN%3Dic%2BSxcyymUqFx9bB8pqSLTGJ-F%3DMHy4PW3eRXw%40mail.gma=
il.com</a><br><br>Thanks<br>Richard<br></div></div>

--0000000000002755a60616d415f1--
--0000000000002755a80616d415f3
Content-Type: application/octet-stream; 
	name="v1-0001-Short-circuit-sort_inner_and_outer-if-there-are-no-mergejoin-clauses.patch"
Content-Disposition: attachment; 
	filename="v1-0001-Short-circuit-sort_inner_and_outer-if-there-are-no-mergejoin-clauses.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lvdljg0f0>
X-Attachment-Id: f_lvdljg0f0

RnJvbSBjMGIwMTdmNjQ5NGE2MGM3ZjUxMDJkNWFmOTQyODE4OGI3YTYxZmZjIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBSaWNoYXJkIEd1byA8Z3VvZmVuZ2xpbnV4QGdtYWlsLmNvbT4K
RGF0ZTogV2VkLCAyNCBBcHIgMjAyNCAwOTowNDo0OCArMDAwMApTdWJqZWN0OiBbUEFUQ0ggdjFd
IFNob3J0LWNpcmN1aXQgc29ydF9pbm5lcl9hbmRfb3V0ZXIgaWYgdGhlcmUgYXJlIG5vCiBtZXJn
ZWpvaW4gY2xhdXNlcwoKSW4gc29ydF9pbm5lcl9hbmRfb3V0ZXIsIHdlIGNyZWF0ZSBtZXJnZWpv
aW4gam9pbiBwYXRocyBieSBleHBsaWNpdGx5CnNvcnRpbmcgYm90aCByZWxhdGlvbnMgb24gZWFj
aCBwb3NzaWJsZSBvcmRlcmluZyBvZiB0aGUgYXZhaWxhYmxlCm1lcmdlam9pbiBjbGF1c2VzLiAg
SG93ZXZlciwgaWYgdGhlcmUgYXJlIG5vIGF2YWlsYWJsZSBtZXJnZWpvaW4KY2xhdXNlcywgd2Ug
Y2FuIHNraXAgdGhpcyBwcm9jZXNzIGVudGlyZWx5LgoKVGhpcyBwYXRjaCBpbnRyb2R1Y2VzIGEg
Y2hlY2sgZm9yIG1lcmdlY2xhdXNlX2xpc3QgYXQgdGhlIGJlZ2lubmluZyBvZgpzb3J0X2lubmVy
X2FuZF9vdXRlciBhbmQgZXhpdHMgdGhlIGZ1bmN0aW9uIGlmIGl0IGlzIGZvdW5kIHRvIGJlIGVt
cHR5LgpUaGlzIG1pZ2h0IGhlbHAgc2tpcCBhbGwgdGhlIHN0YXRlbWVudHMgdGhhdCBjb21lIGJl
Zm9yZSB0aGUgY2FsbCB0bwpzZWxlY3Rfb3V0ZXJfcGF0aGtleXNfZm9yX21lcmdlLCBpbmNsdWRp
bmcgdGhlIGJ1aWxkIG9mIFVuaXF1ZVBhdGggcGF0aHMKaW4gdGhlIGNhc2Ugb2YgSk9JTl9VTklR
VUVfT1VURVIgb3IgSk9JTl9VTklRVUVfSU5ORVIuCi0tLQogc3JjL2JhY2tlbmQvb3B0aW1pemVy
L3BhdGgvam9pbnBhdGguYyB8IDQgKysrKwogMSBmaWxlIGNoYW5nZWQsIDQgaW5zZXJ0aW9ucygr
KQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2pvaW5wYXRoLmMgYi9z
cmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9qb2lucGF0aC5jCmluZGV4IDViZThkYTllMDkuLmRi
YmMxOGU1NmMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2pvaW5wYXRo
LmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvam9pbnBhdGguYwpAQCAtMTI3OCw2
ICsxMjc4LDEwIEBAIHNvcnRfaW5uZXJfYW5kX291dGVyKFBsYW5uZXJJbmZvICpyb290LAogCUxp
c3QJICAgKmFsbF9wYXRoa2V5czsKIAlMaXN0Q2VsbCAgICpsOwogCisJLyogTm90aGluZyB0byBk
byBpZiB0aGVyZSBhcmUgbm8gYXZhaWxhYmxlIG1lcmdlam9pbiBjbGF1c2VzICovCisJaWYgKGV4
dHJhLT5tZXJnZWNsYXVzZV9saXN0ID09IE5JTCkKKwkJcmV0dXJuOworCiAJLyoKIAkgKiBXZSBv
bmx5IGNvbnNpZGVyIHRoZSBjaGVhcGVzdC10b3RhbC1jb3N0IGlucHV0IHBhdGhzLCBzaW5jZSB3
ZSBhcmUKIAkgKiBhc3N1bWluZyBoZXJlIHRoYXQgYSBzb3J0IGlzIHJlcXVpcmVkLiAgV2Ugd2ls
bCBjb25zaWRlcgotLSAKMi4zNC4xCgo=
--0000000000002755a80616d415f3--