From: Chao Li <li.evan.chao@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joel Jacobson <joel@compiler.org>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Improve hash join's handling of tuples with null join keys
Date: Thu, 19 Mar 2026 14:55:04 +0800
Message-ID: <375DCE57-1107-48B7-9A64-3B3FC24CCECD@gmail.com> (raw)
In-Reply-To: <940201.1773861007@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>
<659B2B36-C0FE-4642-921D-E7120E838891@gmail.com>
<52163.1755988628@sss.pgh.pa.us>
<1290278.1772571496@sss.pgh.pa.us>
<3ccec807-d2ff-4150-9585-74cc52926646@app.fastmail.com>
<940201.1773861007@sss.pgh.pa.us>
> On Mar 19, 2026, at 03:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Joel Jacobson" <joel@compiler.org> writes:
>> I've tested v3-0001 and v3-0002 and can confirm the bug introduced
>> in v3-0001 is fixed in v3-0002:
>
> Thanks for testing!
>
> I noticed from the cfbot that this patch wasn't building anymore.
> nodeHash[join].c now need explicit inclusions of utils/tuplestore.h,
> presumably as a result of Alvaro's recent header-inclusion cleanups.
> Here's a v4 that fixes that; no other changes.
>
> We're getting towards the end of the v19 development cycle, so
> I'm planning to push this soon if there's not objections.
>
I remember I reviewed this patch some time ago, so I revisited v4 today and played with it a bit
Overall, this looks like a solid patch to me.
The only thing I noticed is that while 0002 fixes the tuple-counting issue, there does not seem to be a test that would fail without it. So I think it would be better to add a test case for 0002. I tried doing that and attached a diff. Without 0002, the test fails; with 0002, it passes.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/Attachments:nocfbot.add-test-for-count-fix.diff (application/octet-stream, 2.6K) downloaddiff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.outindex 75009e29720..a4689363200 100644--- a/src/test/regress/expected/join_hash.out+++ b/src/test/regress/expected/join_hash.out@@ -49,6 +49,23 @@ begin
end loop;
end;
$$;
+create or replace function hash_actual_rows(query text)+returns int language plpgsql+as+$$+declare+ whole_plan json;+ hash_node json;+begin+ for whole_plan in+ execute 'explain (analyze, format ''json'') ' || query+ loop+ hash_node := find_hash(json_extract_path(whole_plan, '0', 'Plan'));+ return (hash_node->>'Actual Rows')::float::int;+ end loop;+ return null;+end;+$$;
-- Make a simple relation with well distributed keys and correctly
-- estimated size.
create table simple as
@@ -312,6 +329,18 @@ select count(*) from simple r full outer join simple s using (id);
20002
(1 row)
+-- Hash node's rowcount should include null-key rows that are kept for full join+savepoint settings;+set local max_parallel_workers_per_gather = 0;+set local enable_parallel_hash = off;+select hash_actual_rows($$+ select count(*) from simple r full outer join simple s using (id);+$$);+ hash_actual_rows +------------------+ 20001+(1 row)+
rollback to settings;
-- The "bad" case: during execution we need to increase number of
-- batches; in this case we plan for 1 batch, and increase at least a
diff --git a/src/test/regress/sql/join_hash.sql b/src/test/regress/sql/join_hash.sqlindex 989390e6864..4b951310b4e 100644--- a/src/test/regress/sql/join_hash.sql+++ b/src/test/regress/sql/join_hash.sql@@ -53,6 +53,24 @@ begin
end;
$$;
+create or replace function hash_actual_rows(query text)+returns int language plpgsql+as+$$+declare+ whole_plan json;+ hash_node json;+begin+ for whole_plan in+ execute 'explain (analyze, format ''json'') ' || query+ loop+ hash_node := find_hash(json_extract_path(whole_plan, '0', 'Plan'));+ return (hash_node->>'Actual Rows')::float::int;+ end loop;+ return null;+end;+$$;+
-- Make a simple relation with well distributed keys and correctly
-- estimated size.
create table simple as
@@ -190,6 +208,14 @@ $$
$$);
-- parallel full multi-batch hash join
select count(*) from simple r full outer join simple s using (id);
++-- Hash node's rowcount should include null-key rows that are kept for full join+savepoint settings;+set local max_parallel_workers_per_gather = 0;+set local enable_parallel_hash = off;+select hash_actual_rows($$+ select count(*) from simple r full outer join simple s using (id);+$$);
rollback to settings;
-- The "bad" case: during execution we need to increase number of
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: li.evan.chao@gmail.com, tgl@sss.pgh.pa.us, joel@compiler.org
Subject: Re: Improve hash join's handling of tuples with null join keys
In-Reply-To: <375DCE57-1107-48B7-9A64-3B3FC24CCECD@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> <616751.1755276726@sss.pgh.pa.us> <5C5EE031-F086-4353-A17A-DA563CD24DDD@gmail.com> <463296.1755553046@sss.pgh.pa.us> <659B2B36-C0FE-4642-921D-E7120E838891@gmail.com> <52163.1755988628@sss.pgh.pa.us> <1290278.1772571496@sss.pgh.pa.us> <3ccec807-d2ff-4150-9585-74cc52926646@app.fastmail.com> <940201.1773861007@sss.pgh.pa.us> <375DCE57-1107-48B7-9A64-3B3FC24CCECD@gmail.com>
* 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