From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chao Li <li.evan.chao@gmail.com>
cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Improve hash join's handling of tuples with null join keys
In-reply-to: <544D7C83-CECE-44E7-B5D7-530E9318D231@gmail.com>
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>
Comments: In-reply-to Chao Li <li.evan.chao@gmail.com>
	message dated "Thu, 14 Aug 2025 10:36:06 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <616750.1755276726.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 15 Aug 2025 12:52:06 -0400
Message-ID: <616751.1755276726@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/616751.1755276726%40sss.pgh.pa.us>
Precedence: bulk

Chao Li <li.evan.chao@gmail.com> writes:
> With this patch, =E2=80=9Cisnull=E2=80=9D now becomes true because of th=
e change of strict op. Then the outer null join key tuple must be stored i=
n a tuplestore. When an outer table contains a lot of null join key tuples=
, then the tuplestore could bump to very large, in that case, it would be =
hard to say this patch really benefits.

What's your point?  If we don't divert those tuples into the
tuplestore, then they will end up in the main hash table instead,
and the consequences of bloat there are far worse.

> Based on this patch, if we are doing a left join, and outer table is emp=
ty, then all tuples from the inner table should be returned. In that case,=
 we can skip building a hash table, instead, we can put all inner table tu=
ples into hashtable.innerNullTupleStore. Building a tuplestore should be c=
heaper than building a hash table, so this way makes a little bit more per=
formance improvement.

I think that would make the logic completely unintelligible.  Also,
a totally-empty input relation is not a common situation.  We try to
optimize such cases when it's simple to do so, but we shouldn't let
that drive the fundamental design.

			regards, tom lane