From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <659B2B36-C0FE-4642-921D-E7120E838891@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_0F250CA4-2D85-40B6-8CB0-7A2FCCDA83E1"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: Improve hash join's handling of tuples with null join keys
Date: Tue, 19 Aug 2025 08:58:47 +0800
In-Reply-To: <463296.1755553046@sss.pgh.pa.us>
Cc: pgsql-hackers@lists.postgresql.org
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <3061845.1746486714@sss.pgh.pa.us>
 <496221.1748882849@sss.pgh.pa.us>
 <175507656113.993.1381684440543440253.pgcf@coridan.postgresql.org>
 <544D7C83-CECE-44E7-B5D7-530E9318D231@gmail.com>
 <616751.1755276726@sss.pgh.pa.us>
 <5C5EE031-F086-4353-A17A-DA563CD24DDD@gmail.com>
 <463296.1755553046@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/659B2B36-C0FE-4642-921D-E7120E838891%40gmail.com>
Precedence: bulk


--Apple-Mail=_0F250CA4-2D85-40B6-8CB0-7A2FCCDA83E1
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii


> On Aug 19, 2025, at 05:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>=20
>=20
> Yeah, we could make multi-batch PHJ do this differently from the other
> cases, but I don't want to go there: too much complication and risk of
> bugs for what is a purely hypothetical performance issue.  Besides
> which, if the join is large enough to be worth worrying over, it's
> most likely taking that code path anyhow.
>=20
>=20
>> We can simply added a new flag to HashTable, say named =
skip_building_hash. Upon right join (join to the hash side), and outer =
table is empty, set the flag to true, then in the =
MultiExecPrivateHash(), if skip_building_hash is true, directly put all =
tuples into node->null_tuple_store without building a hash table.
>> Then in ExecHashJoinImpl(), after "(void) MultiExecProcNode()" is =
called, if hashtable->skip_building_hash is true, directly set =
node->hj_JoinState =3D HJ_FILL_INNER_NULL_TUPLES.
>=20
> I'm not excited about this idea either.  It's completely abusing the
> data structure, because the "null_tuple_store" is now being used for
> tuples that (probably) don't have null join keys.  The fact that you
> could cram it in with not very many lines of code does not mean that
> the result will be understandable or maintainable --- and certainly,
> hash join is on the hairy edge of being too complicated already.
>=20
> 			regards, tom lane


Thanks for the explanation. Then these two comments are resolved.

--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_0F250CA4-2D85-40B6-8CB0-7A2FCCDA83E1
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=us-ascii

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dus-ascii"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;"><br =
id=3D"lineBreakAtBeginningOfMessage"><div><br><blockquote =
type=3D"cite"><div>On Aug 19, 2025, at 05:37, Tom Lane =
&lt;tgl@sss.pgh.pa.us&gt; wrote:</div><div><div><blockquote =
type=3D"cite"><br></blockquote><br>Yeah, we could make multi-batch PHJ =
do this differently from the other<br>cases, but I don't want to go =
there: too much complication and risk of<br>bugs for what is a purely =
hypothetical performance issue. &nbsp;Besides<br>which, if the join is =
large enough to be worth worrying over, it's<br>most likely taking that =
code path anyhow.<br><br><br><blockquote type=3D"cite">We can simply =
added a new flag to HashTable, say named skip_building_hash. Upon right =
join (join to the hash side), and outer table is empty, set the flag to =
true, then in the MultiExecPrivateHash(), if skip_building_hash is true, =
directly put all tuples into node-&gt;null_tuple_store without building =
a hash table.<br>Then in ExecHashJoinImpl(), after "(void) =
MultiExecProcNode()" is called, if hashtable-&gt;skip_building_hash is =
true, directly set node-&gt;hj_JoinState =3D =
HJ_FILL_INNER_NULL_TUPLES.<br></blockquote><br>I'm not excited about =
this idea either. &nbsp;It's completely abusing the<br>data structure, =
because the "null_tuple_store" is now being used for<br>tuples that =
(probably) don't have null join keys. &nbsp;The fact that you<br>could =
cram it in with not very many lines of code does not mean that<br>the =
result will be understandable or maintainable --- and certainly,<br>hash =
join is on the hairy edge of being too complicated already.<br><br><span =
class=3D"Apple-tab-span" style=3D"white-space:pre">	</span><span =
class=3D"Apple-tab-span" style=3D"white-space:pre">	</span><span =
class=3D"Apple-tab-span" style=3D"white-space:pre">	</span>regards, =
tom lane<br></div></div></blockquote></div><div><br></div><div>Thanks =
for the explanation. Then these two comments are =
resolved.</div><br><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; overflow-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div>--</div><div>Chao Li (Evan)<br>HighGo Software =
Co., Ltd.<br>https://www.highgo.com/</div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">
</div>
<br></body></html>=

--Apple-Mail=_0F250CA4-2D85-40B6-8CB0-7A2FCCDA83E1--