pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) download diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out index 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.sql index 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

view thread (19+ messages)  latest in thread

Message-ID: <375DCE57-1107-48B7-9A64-3B3FC24CCECD@gmail.com>
Permalink:  ../375DCE57-1107-48B7-9A64-3B3FC24CCECD@gmail.com/
Also on:    postgresql.org/message-id/375DCE57-1107-48B7-9A64-3B3FC24CCECD@gmail.com

 ·  · 

reply

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