Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w37II-000uo7-0t for pgsql-hackers@arkaria.postgresql.org; Thu, 19 Mar 2026 06:55:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w37IG-00GqBi-1o for pgsql-hackers@arkaria.postgresql.org; Thu, 19 Mar 2026 06:55:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w37IG-00GqBa-0r for pgsql-hackers@lists.postgresql.org; Thu, 19 Mar 2026 06:55:44 +0000 Received: from mail-pl1-x62d.google.com ([2607:f8b0:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w37ID-000000014qv-2YAA for pgsql-hackers@lists.postgresql.org; Thu, 19 Mar 2026 06:55:44 +0000 Received: by mail-pl1-x62d.google.com with SMTP id d9443c01a7336-2ad21f437eeso5150845ad.0 for ; Wed, 18 Mar 2026 23:55:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773903340; x=1774508140; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=ldXrv6/gN/4s6zvhMJ4lvJ64EAbN9+sXuziwgN8GCwM=; b=BtgsRJmQYQJV955jiPBq7UQUmSgRnLRVc3xYxs3grfMQ3HVULvzqGOLXcAnXeOTQPd 3SFvSsGkRvm7yIwtV7pjUbX1Q615dFCBhpPCmnPjQHwnU72zZvIaVyllE9ogMAF+gujU Hu0BE1y78YuI/tqETwqdjZj6cUoUb4NaIe+9wnu2VapKfzXByA4QtQLSXwBofnsF8SBD y5BwLtfrBOxq6sK+DKIH4xGqHukrwJzwLJECe3L+8H4q59Lk3Ee/6/ZKJXiZ2pRbNTo1 4wQKxITYE79reVfuAYPPoby9pvrvgbMFr+bBJUu0g1pdatKlE/w3VmdjWZ3BDTzGIxDm HkRg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1773903340; x=1774508140; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ldXrv6/gN/4s6zvhMJ4lvJ64EAbN9+sXuziwgN8GCwM=; b=UHlwwYKiemGa1GN8kXnuV2kUA0tALpta+0jTOoOxfjPSoQBN88PFfbrYF6qc+A4Dkb TdOysmfQjzW5pJnKlf3Lu8XLztoQEqHf5a3xIulK/GF5bac/8h7tdR2RWPnzeqShkm5c zQyfDmqEMvTHd3rqQD2+1Wh6yyuS7TluEG32SlrfYGbpeQjHUlgt+MkUEx7toAFRD3vl jBK2W8sUBVPk2B4XESd0uDzTJbYGcreFI/w2er0dwaBE74txxDBR9D9nm6p17Bq802jH NM6M6cwZRdUI0DqYvWIeQ420XR9aK4HkBxJvVAmqT/oKKDLSEJfpQGMteMKdhWYDgz8+ hHog== X-Forwarded-Encrypted: i=1; AJvYcCXnyvv0UxhNLIuDQtGTXMZ56JIrvhXULkLEHnX1BWUPFRieo+5b/LIF4QsJaodJU8i93uZOCzGjstAxujCf@lists.postgresql.org X-Gm-Message-State: AOJu0YwPc99bB05Tqqv4ax93hIlfIwP+AkgAmZJbkyM1jdnokSYCH0uJ soiyJ8CMFN5J2Z2c9C7tkRXBU7TgsQ3/1lg/L8wCEZxjZMa40iAxbch0 X-Gm-Gg: ATEYQzyBBNqAWZsVqYU5d7EumBuuPFmixsdiVDnID+Pi9RFKSgxve8n53chiJrb2v+l 4EWhmbMhFYLmNtRU3p0VdcNq+OEFvr8RfzJuty94Z4OLTDvrj3LKCwpB5CQI0PMI+W6ax9HQz1B X02JU49crNJqieYArPv0q5+RWc4bznu67Sg3F/DAiH9iXegL0uYFUfE/Ce+M+WRdmwRARBmUiEX MPrvJXYpGKgLJErw+YiYHjzu2TjS18KYFFwPhGhPInql4GDQL34BoHk/8dAIcGi8qBiCa9B5YNr fbayl0l0EjcHn5Uh43j+QlM71Mef8O9U3OH8HWiO/WbPZgggsQLI5EyzoOcck3sj1okt9WOfZ6E VE0hzJzeeEf6Y9upe+lufKyHD+5CT3cWKJrM7XdBxdmY7qLlRtdwN225chf2ujnlv7YkLvADoG2 mqzcJmb5fdmkbeX+sHXDt074UXIQdQbA== X-Received: by 2002:a17:902:da82:b0:2b0:4d5f:1f6e with SMTP id d9443c01a7336-2b0771a5489mr21987315ad.8.1773903340044; Wed, 18 Mar 2026 23:55:40 -0700 (PDT) Received: from smtpclient.apple ([203.10.98.27]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-2b06f880b70sm59134345ad.71.2026.03.18.23.55.37 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 18 Mar 2026 23:55:39 -0700 (PDT) From: Chao Li Message-Id: <375DCE57-1107-48B7-9A64-3B3FC24CCECD@gmail.com> Content-Type: multipart/mixed; boundary="Apple-Mail=_25DFC27C-9697-4A58-8758-6058B7B5E1A8" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\)) Subject: Re: Improve hash join's handling of tuples with null join keys Date: Thu, 19 Mar 2026 14:55:04 +0800 In-Reply-To: <940201.1773861007@sss.pgh.pa.us> Cc: Joel Jacobson , pgsql-hackers@lists.postgresql.org To: Tom Lane 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> X-Mailer: Apple Mail (2.3864.400.21) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_25DFC27C-9697-4A58-8758-6058B7B5E1A8 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Mar 19, 2026, at 03:10, Tom Lane wrote: >=20 > "Joel Jacobson" writes: >> I've tested v3-0001 and v3-0002 and can confirm the bug introduced >> in v3-0001 is fixed in v3-0002: >=20 > Thanks for testing! >=20 > 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. >=20 > We're getting towards the end of the v19 development cycle, so > I'm planning to push this soon if there's not objections. >=20 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/ --Apple-Mail=_25DFC27C-9697-4A58-8758-6058B7B5E1A8 Content-Disposition: attachment; filename=nocfbot.add-test-for-count-fix.diff Content-Type: application/octet-stream; x-unix-mode=0644; name="nocfbot.add-test-for-count-fix.diff" Content-Transfer-Encoding: 7bit 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 --Apple-Mail=_25DFC27C-9697-4A58-8758-6058B7B5E1A8--