agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Fix Hash Join performance issue when hashing NULL values 3+ messages / 1 participants [nested] [flat]
* pgsql: Fix Hash Join performance issue when hashing NULL values @ 2026-07-31 11:24 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: David Rowley @ 2026-07-31 11:24 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix Hash Join performance issue when hashing NULL values adf97c156 allowed expression evaluation to perform hashing, and subsequently 9ca67658d fixed a memory stomping bug in that commit that caused unrelated-to-hashing expression op steps to stomp on the intermediate hash value. The intermediate hash value needs to be maintained when hashing multiple hash keys. 9ca67658d didn't quite get things right when in "strict" mode when it aborted hashing early after encountering a NULL hash key. What was meant to happen was that the expression returns NULL directly to indicate to the caller the value hashed to NULL. The problem was that any EEOP_HASHDATUM_FIRST_STRICT or EEOP_HASHDATUM_NEXT32_STRICT op step that didn't belong to the final key to be hashed would have its op->resnull and op->resvalue pointing to the location to store the intermediate hash value. That's correct for non-NULLs since we bit-rotate the intermediate value and continue hashing, but with the strict case, when we get a NULL key, we immediately jump to the "jumpdone" step. The problem is the jumpdone step expects the ExprState resnull and resvalue fields to be set (as they would be if we didn't abort hashing early due to the NULL), but when we aborted early, the ExprState fields never got set. This would result in inserting records into the hash table that would never match to any join partner, which is a waste of CPU and memory. Here we fix this by having EEOP_HASHDATUM_FIRST_STRICT and EEOP_HASHDATUM_NEXT32_STRICT populate the ExprState resnull and resvalue fields directly when the value to hash is NULL. Although Hash Agg and Hashed Subplans do use hashing from ExprStates, those were unaffected by this bug, as neither of those uses the STRICT op steps. Thanks to Tomas Vondra for finding the offending commit. Reported-by: Dan Stefura <dstefura@bluecatnetworks.com> Author: David Rowley <dgrowleyml@gmail.com> Discussion: https://postgr.es/m/YQBPR0101MB89738FB972FBD02A3640C6D3D6C92@YQBPR0101MB8973.CANPRD01.PROD.OUTLOOK.C... Backpatch-through: 18 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/60826a352d497b15a30de29b7796d0c2d097a0e3 Modified Files -------------- src/backend/executor/execExprInterp.c | 8 ++++---- src/backend/jit/llvm/llvmjit_expr.c | 9 +++++---- 2 files changed, 9 insertions(+), 8 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix Hash Join performance issue when hashing NULL values @ 2026-07-31 11:24 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: David Rowley @ 2026-07-31 11:24 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix Hash Join performance issue when hashing NULL values adf97c156 allowed expression evaluation to perform hashing, and subsequently 9ca67658d fixed a memory stomping bug in that commit that caused unrelated-to-hashing expression op steps to stomp on the intermediate hash value. The intermediate hash value needs to be maintained when hashing multiple hash keys. 9ca67658d didn't quite get things right when in "strict" mode when it aborted hashing early after encountering a NULL hash key. What was meant to happen was that the expression returns NULL directly to indicate to the caller the value hashed to NULL. The problem was that any EEOP_HASHDATUM_FIRST_STRICT or EEOP_HASHDATUM_NEXT32_STRICT op step that didn't belong to the final key to be hashed would have its op->resnull and op->resvalue pointing to the location to store the intermediate hash value. That's correct for non-NULLs since we bit-rotate the intermediate value and continue hashing, but with the strict case, when we get a NULL key, we immediately jump to the "jumpdone" step. The problem is the jumpdone step expects the ExprState resnull and resvalue fields to be set (as they would be if we didn't abort hashing early due to the NULL), but when we aborted early, the ExprState fields never got set. This would result in inserting records into the hash table that would never match to any join partner, which is a waste of CPU and memory. Here we fix this by having EEOP_HASHDATUM_FIRST_STRICT and EEOP_HASHDATUM_NEXT32_STRICT populate the ExprState resnull and resvalue fields directly when the value to hash is NULL. Although Hash Agg and Hashed Subplans do use hashing from ExprStates, those were unaffected by this bug, as neither of those uses the STRICT op steps. Thanks to Tomas Vondra for finding the offending commit. Reported-by: Dan Stefura <dstefura@bluecatnetworks.com> Author: David Rowley <dgrowleyml@gmail.com> Discussion: https://postgr.es/m/YQBPR0101MB89738FB972FBD02A3640C6D3D6C92@YQBPR0101MB8973.CANPRD01.PROD.OUTLOOK.C... Backpatch-through: 18 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a71a348ed1e081d3bd32f0b1f9c177f44f9a822a Modified Files -------------- src/backend/executor/execExprInterp.c | 8 ++++---- src/backend/jit/llvm/llvmjit_expr.c | 9 +++++---- 2 files changed, 9 insertions(+), 8 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix Hash Join performance issue when hashing NULL values @ 2026-07-31 11:25 David Rowley <drowley@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: David Rowley @ 2026-07-31 11:25 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix Hash Join performance issue when hashing NULL values adf97c156 allowed expression evaluation to perform hashing, and subsequently 9ca67658d fixed a memory stomping bug in that commit that caused unrelated-to-hashing expression op steps to stomp on the intermediate hash value. The intermediate hash value needs to be maintained when hashing multiple hash keys. 9ca67658d didn't quite get things right when in "strict" mode when it aborted hashing early after encountering a NULL hash key. What was meant to happen was that the expression returns NULL directly to indicate to the caller the value hashed to NULL. The problem was that any EEOP_HASHDATUM_FIRST_STRICT or EEOP_HASHDATUM_NEXT32_STRICT op step that didn't belong to the final key to be hashed would have its op->resnull and op->resvalue pointing to the location to store the intermediate hash value. That's correct for non-NULLs since we bit-rotate the intermediate value and continue hashing, but with the strict case, when we get a NULL key, we immediately jump to the "jumpdone" step. The problem is the jumpdone step expects the ExprState resnull and resvalue fields to be set (as they would be if we didn't abort hashing early due to the NULL), but when we aborted early, the ExprState fields never got set. This would result in inserting records into the hash table that would never match to any join partner, which is a waste of CPU and memory. Here we fix this by having EEOP_HASHDATUM_FIRST_STRICT and EEOP_HASHDATUM_NEXT32_STRICT populate the ExprState resnull and resvalue fields directly when the value to hash is NULL. Although Hash Agg and Hashed Subplans do use hashing from ExprStates, those were unaffected by this bug, as neither of those uses the STRICT op steps. Thanks to Tomas Vondra for finding the offending commit. Reported-by: Dan Stefura <dstefura@bluecatnetworks.com> Author: David Rowley <dgrowleyml@gmail.com> Discussion: https://postgr.es/m/YQBPR0101MB89738FB972FBD02A3640C6D3D6C92@YQBPR0101MB8973.CANPRD01.PROD.OUTLOOK.C... Backpatch-through: 18 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/f70acc8a2b96e0b565836799b6ccc3bacba8068a Modified Files -------------- src/backend/executor/execExprInterp.c | 8 ++++---- src/backend/jit/llvm/llvmjit_expr.c | 9 +++++---- 2 files changed, 9 insertions(+), 8 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-07-31 11:25 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-07-31 11:24 pgsql: Fix Hash Join performance issue when hashing NULL values David Rowley <drowley@postgresql.org> 2026-07-31 11:24 pgsql: Fix Hash Join performance issue when hashing NULL values David Rowley <drowley@postgresql.org> 2026-07-31 11:25 pgsql: Fix Hash Join performance issue when hashing NULL values David Rowley <drowley@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox