pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
10+ messages / 5 participants
[nested] [flat]

Commits from this thread:
  2026-11-17 0c6d95e134 7646ab9384 9107a0feaf  Resolve mismatch between JIT and non-JIT hashing
* PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-16 11:26  Tor Erik Linnerud <tel@jklm.no>
  0 siblings, 2 replies; 10+ messages in thread

From: Tor Erik Linnerud @ 2026-11-16 11:26 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

On 18.6, a hash aggregate grouping by an int4range column followed by
another column is several hundred times slower than on 17 when JIT is used.
Same data, official Docker images, jit_above_cost = 0:

GROUP BY p, r, b (range not last):
17.12: 0.48 s
18.6: 188 s

GROUP BY p, b, r (range last):
17.12: 0.45 s
18.6: 0.39 s

With jit = off, 18.6 is fast.

Likely cause: in llvmjit_expr.c, EEOP_HASHDATUM_NEXT32 combines the hash
function's full 64-bit Datum. hash_range returns its hash via
PG_RETURN_INT32, which sign-extends, and the next column's rotate then
shifts those upper bits into the combined hash, wiping it. The interpreter
truncates to 32 bits and is not affected.

Reproduction:

create table t as
select (random() * 1000)::int as p, random() < 0.5 as b,
int4range(d, d + 10) as r
from (select (random() * 20)::int * 10 as d from generate_series(1,
1000000)) s;
analyze t;
set max_parallel_workers_per_gather = 0;
set enable_sort = off;
set jit = on;
set jit_above_cost = 0;

-- slow on 18: range key followed by another key
select count(*) from (select p, r, b from t group by 1, 2, 3) s;

-- fast: same groups, range key last
select count(*) from (select p, b, r from t group by 1, 2, 3) s;

Best,
Tor Erik

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-16 16:29  Andres Freund <andres@anarazel.de>
  parent: Tor Erik Linnerud <tel@jklm.no>
  1 sibling, 1 reply; 10+ messages in thread

From: Andres Freund @ 2026-11-16 16:29 UTC (permalink / raw)
  To: Tor Erik Linnerud <tel@jklm.no>; David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org

Hi,

On 2026-11-16 12:26:36 +0100, Tor Erik Linnerud wrote:
> On 18.6, a hash aggregate grouping by an int4range column followed by
> another column is several hundred times slower than on 17 when JIT is used.
> Same data, official Docker images, jit_above_cost = 0:
>
> GROUP BY p, r, b (range not last):
> 17.12: 0.48 s
> 18.6: 188 s
>
> GROUP BY p, b, r (range last):
> 17.12: 0.45 s
> 18.6: 0.39 s
>
> With jit = off, 18.6 is fast.
>
> Likely cause: in llvmjit_expr.c, EEOP_HASHDATUM_NEXT32 combines the hash
> function's full 64-bit Datum. hash_range returns its hash via
> PG_RETURN_INT32, which sign-extends, and the next column's rotate then
> shifts those upper bits into the combined hash, wiping it. The interpreter
> truncates to 32 bits and is not affected.

That's obviously a bug.  Presumably in

2024-08-20  adf97c15623  Speed up Hash Join by making ExprStates support hashing

CCing David.

Greetings,

Andres Freund






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-16 20:16  David Rowley <dgrowleyml@gmail.com>
  parent: Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 10+ messages in thread

From: David Rowley @ 2026-11-16 20:16 UTC (permalink / raw)
  To: Andres Freund <andres@anarazel.de>; +Cc: Tor Erik Linnerud <tel@jklm.no>; pgsql-bugs@lists.postgresql.org

On Tue, 17 Nov 2026 at 05:29, Andres Freund <andres@anarazel.de> wrote:
> On 2026-11-16 12:26:36 +0100, Tor Erik Linnerud wrote:
> > Likely cause: in llvmjit_expr.c, EEOP_HASHDATUM_NEXT32 combines the hash
> > function's full 64-bit Datum. hash_range returns its hash via
> > PG_RETURN_INT32, which sign-extends, and the next column's rotate then
> > shifts those upper bits into the combined hash, wiping it. The interpreter
> > truncates to 32 bits and is not affected.
>
> That's obviously a bug.  Presumably in
>
> 2024-08-20  adf97c15623  Speed up Hash Join by making ExprStates support hashing
>
> CCing David.

Thanks. Looking at this now.

David






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-16 22:34  David Rowley <dgrowleyml@gmail.com>
  parent: Tor Erik Linnerud <tel@jklm.no>
  1 sibling, 1 reply; 10+ messages in thread

From: David Rowley @ 2026-11-16 22:34 UTC (permalink / raw)
  To: Tor Erik Linnerud <tel@jklm.no>; +Cc: pgsql-bugs@lists.postgresql.org

On Tue, 17 Nov 2026 at 00:27, Tor Erik Linnerud <tel@jklm.no> wrote:
> create table t as
> select (random() * 1000)::int as p, random() < 0.5 as b,
> int4range(d, d + 10) as r
> from (select (random() * 20)::int * 10 as d from generate_series(1, 1000000)) s;
> analyze t;
> set max_parallel_workers_per_gather = 0;
> set enable_sort = off;
> set jit = on;
> set jit_above_cost = 0;
>
> -- slow on 18: range key followed by another key
> select count(*) from (select p, r, b from t group by 1, 2, 3) s;

Many thanks for the clear and concise report and reproducer. That's
becoming rare.

The attached patch resolves the issue. I had to use a bit of LLM as I
previously didn't find the proper way to trim the result to 32-bit. I
had been using LLVMBuildAnd with l_datum_const(0xffffffff). The
documentation for all this stuff seems nonexistent. The LLM tells I
should have used LLVMBuildTrunc(), which the patch does, and now I'm
truncating before the shift and xor then doing the zero-extend back to
64-bit before storing the result.

I did some analysis when patched with:

inline patch 1: src/backend/executor/execGrouping.c (+0/-0 lines) download inline-1.patch--- a/src/backend/executor/execGrouping.c +++ b/src/backend/executor/execGrouping.c @@ -413,6 +413,7 @@ LookupTupleHashEntry(TupleHashTable hashtable,
TupleTableSlot *slot, hashtable->cur_eq_func = hashtable->tab_eq_func; local_hash = TupleHashTableHash_internal(hashtable->hashtab, NULL); + elog(NOTICE, "%u", local_hash); entry = LookupTupleHashEntry_internal(hashtable, slot, isnew, local_hash); if (hash != NULL) and diffed the output with jit=1 vs jit=0. Running your query now produces the same hash values with and without jit. David Patches and attachments: download all (2 files, .tar.gz)
v1-0001-Resolve-mismatch-between-JIT-and-non-JIT-hashing.patch (application/octet-stream, 4.6K) download From bcffbbaa7ca6691a1d6883e08e5d9f2dbf5e21c8 Mon Sep 17 00:00:00 2001 From: David Rowley <dgrowleyml@gmail.com> Date: Tue, 17 Nov 2026 10:23:02 +1300 Subject: [PATCH v1] Resolve mismatch between JIT and non-JIT hashing adf97c156 taught expression evaluation to produce hash values. That required both JIT and non-JIT versions. The JIT version wasn't quite correct as it didn't truncate the produced hash value down to 32 bits before rotating and combining with the previously stored intermediate hash value when hashing multiple keys. When a hash function returned a negative int32, the value was sign-extended to 64 bits, and the JIT code's 1-bit left rotation then shifted those set upper bits into the lower 32, setting every bit of the intermediate hash to 1 no matter what the earlier keys hashed to. This meant all rows for which an earlier key had a negative hash value effectively hashed only on the remaining keys, causing many hash collisions resulting in slow hash aggregates and hash joins. Here we fix by adjusting the JIT code to match what the non-JIT version does. Backpatch to 18, where adf97c156 was introduced. Reported-by: Tor Erik Linnerud <tel@jklm.no> Author: David Rowley <dgrowleyml@gmail.com> Discussion: https://postgr.es/m/CAEGP1v12rRmZo1h3KZ+ZCS66A9xLoSOuLAyBXu92nd97E3jJPw@mail.gmail.com Backpatch-through: 18 --- src/backend/jit/llvm/llvmjit_expr.c | 41 ++++++++++++++++++----------- 1 file changed, 25 insertions(+), 16 deletions(-) diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index fc80d3e55fe..2f3742c56f3 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -2060,17 +2060,14 @@ llvm_compile_expr(ExprState *state) * previous hash operation stored it. */ v_prevhash = l_load(b, TypeDatum, tmp, "prevhash"); + v_prevhash = LLVMBuildTrunc(b, v_prevhash, + LLVMInt32TypeInContext(lc), ""); - /* - * Rotate bits left by 1 bit. Be careful not to - * overflow uint32 when working with Datum. - */ - v_tmp1 = LLVMBuildShl(b, v_prevhash, l_datum_const(1), + /* Rotate the 32-bit hash value left by 1 bit */ + v_tmp1 = LLVMBuildShl(b, v_prevhash, l_int32_const(lc, 1), ""); - v_tmp1 = LLVMBuildAnd(b, v_tmp1, - l_datum_const(0xffffffff), ""); v_tmp2 = LLVMBuildLShr(b, v_prevhash, - l_datum_const(31), ""); + l_int32_const(lc, 31), ""); v_prevhash = LLVMBuildOr(b, v_tmp1, v_tmp2, "rotatedhash"); } @@ -2136,7 +2133,9 @@ llvm_compile_expr(ExprState *state) * Save the rotated hash value and skip to the * next op. */ - LLVMBuildStore(b, v_prevhash, v_resvaluep); + LLVMBuildStore(b, + LLVMBuildZExt(b, v_prevhash, TypeDatum, ""), + v_resvaluep); } else { @@ -2188,15 +2187,18 @@ llvm_compile_expr(ExprState *state) v_prevhash = l_load(b, TypeDatum, tmp, "prevhash"); /* - * Rotate bits left by 1 bit. Be careful not to - * overflow uint32 when working with Datum. + * Truncate the value to 32 bits. This is equivalent + * to the DatumGetUInt32 in execExprInterp.c's + * EEOP_HASHDATUM_NEXT32_STRICT case. */ - v_tmp1 = LLVMBuildShl(b, v_prevhash, l_datum_const(1), + v_prevhash = LLVMBuildTrunc(b, v_prevhash, + LLVMInt32TypeInContext(lc), ""); + + /* Rotate the 32-bit hash value left by 1 bit */ + v_tmp1 = LLVMBuildShl(b, v_prevhash, l_int32_const(lc, 1), ""); - v_tmp1 = LLVMBuildAnd(b, v_tmp1, - l_datum_const(0xffffffff), ""); v_tmp2 = LLVMBuildLShr(b, v_prevhash, - l_datum_const(31), ""); + l_int32_const(lc, 31), ""); v_prevhash = LLVMBuildOr(b, v_tmp1, v_tmp2, "rotatedhash"); } @@ -2207,12 +2209,19 @@ llvm_compile_expr(ExprState *state) /* * For NEXT32 ops, XOR (^) the returned hash value with - * the existing hash value. + * the existing hash value. We truncate to 32 bits to + * match the DatumGetUInt32() usages in execExprInterp.c's + * EEOP_HASHDATUM_NEXT32_* cases. */ if (opcode == EEOP_HASHDATUM_NEXT32 || opcode == EEOP_HASHDATUM_NEXT32_STRICT) + { + v_retval = LLVMBuildTrunc(b, v_retval, + LLVMInt32TypeInContext(lc), ""); v_retval = LLVMBuildXor(b, v_prevhash, v_retval, "xorhash"); + v_retval = LLVMBuildZExt(b, v_retval, TypeDatum, ""); + } LLVMBuildStore(b, v_retval, v_resvaluep); LLVMBuildStore(b, l_sbool_const(0), v_resnullp); -- 2.53.0
^ permalink raw reply [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-17 10:03  Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  parent: David Rowley <dgrowleyml@gmail.com>
  0 siblings, 2 replies; 10+ messages in thread

From: Ayush Tiwari @ 2026-11-17 10:03 UTC (permalink / raw)
  To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tor Erik Linnerud <tel@jklm.no>; pgsql-bugs@lists.postgresql.org

Hi,

On Tue, 17 Nov 2026 at 04:04, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Tue, 17 Nov 2026 at 00:27, Tor Erik Linnerud <tel@jklm.no> wrote:
> > create table t as
> > select (random() * 1000)::int as p, random() < 0.5 as b,
> > int4range(d, d + 10) as r
> > from (select (random() * 20)::int * 10 as d from generate_series(1,
1000000)) s;
> > analyze t;
> > set max_parallel_workers_per_gather = 0;
> > set enable_sort = off;
> > set jit = on;
> > set jit_above_cost = 0;
> >
> > -- slow on 18: range key followed by another key
> > select count(*) from (select p, r, b from t group by 1, 2, 3) s;
>
> Many thanks for the clear and concise report and reproducer. That's
> becoming rare.
>
> The attached patch resolves the issue. I had to use a bit of LLM as I
> previously didn't find the proper way to trim the result to 32-bit. I
> had been using LLVMBuildAnd with l_datum_const(0xffffffff). The
> documentation for all this stuff seems nonexistent.

Yeah, I was looking into this yesterday after the report came in to see
if I could find some docs on it, and couldn't find much on the C API
either.  The only useful thing I found was the LLVM LangRef for trunc
[1] and zext [2].

> The LLM tells I
> should have used LLVMBuildTrunc(), which the patch does, and now I'm
> truncating before the shift and xor then doing the zero-extend back to
> 64-bit before storing the result.

The fix lgtm.  I tried Tor Erik's repro on master (LLVM 18) and it went
from timing out (>2m) down to ~0.5s.

>
> I did some analysis when patched with:
>
> --- a/src/backend/executor/execGrouping.c
> +++ b/src/backend/executor/execGrouping.c
> @@ -413,6 +413,7 @@ LookupTupleHashEntry(TupleHashTable hashtable,
> TupleTableSlot *slot,
>         hashtable->cur_eq_func = hashtable->tab_eq_func;
>
>         local_hash = TupleHashTableHash_internal(hashtable->hashtab,
NULL);
> +       elog(NOTICE, "%u", local_hash);
>         entry = LookupTupleHashEntry_internal(hashtable, slot, isnew,
> local_hash);
>
>         if (hash != NULL)
>
> and diffed the output with jit=1 vs jit=0. Running your query now
> produces the same hash values with and without jit.

Should we be adding a test case for this? Basically comparing
HashAgg / multi-batch HashJoin output order with jit on/off.

Regards,
Ayush

[1] https://llvm.org/docs/LangRef.html#trunc-to-instruction
[2] https://llvm.org/docs/LangRef.html#zext-to-instruction

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-17 14:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  1 sibling, 1 reply; 10+ messages in thread

From: Tom Lane @ 2026-11-17 14:49 UTC (permalink / raw)
  To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Tor Erik Linnerud <tel@jklm.no>; pgsql-bugs@lists.postgresql.org

Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
> Should we be adding a test case for this? Basically comparing
> HashAgg / multi-batch HashJoin output order with jit on/off.

That's not gonna work on BF animals without JIT.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-17 23:14  David Rowley <dgrowleyml@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: David Rowley @ 2026-11-17 23:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; Tor Erik Linnerud <tel@jklm.no>; pgsql-bugs@lists.postgresql.org

On Wed, 18 Nov 2026 at 03:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
> > Should we be adding a test case for this? Basically comparing
> > HashAgg / multi-batch HashJoin output order with jit on/off.
>
> That's not gonna work on BF animals without JIT.

I don't think that's really the issue here. Those animals are free to
do something with SET jit=1; then the same thing again with SET jit=0;
and compare the two. They'd just be doing the same thing each time and
the tests probably would never fail.

IMO, the problems are:

1) There is no way to get the hash value of an ExprState directly in
SQL; therefore;
2) The only current way to test this is to create a test that checks
for slow performance or a Hash Aggregate or Hash Join, and doing that
is a terrible idea.

For #1, it might be possible to do a test module with a function like
CREATE FUNCTION test_hash_exprs(seed int4, strict bool, VARIADIC
"any") which takes the variadic args and uses ExecBuildHash32Expr() to
build an ExprState and returns the hashvalue / isnull result. I guess
then we could run that on a table with a few columns and insert into a
temp table, then run again with jit=1 and check the results match.
Perhaps the function could even create two ExprStates, one with a
faked-up PlanState to trick jit_compile_expr() into compiling the
expression when there's a JIT provider. It could then return both
values and allow the caller to compare them.

I had an LLM draft most of that up locally. I'm just wondering if it's
worth pursuing or not.

David






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-17 23:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David Rowley <dgrowleyml@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2026-11-17 23:42 UTC (permalink / raw)
  To: David Rowley <dgrowleyml@gmail.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; Tor Erik Linnerud <tel@jklm.no>; pgsql-bugs@lists.postgresql.org

David Rowley <dgrowleyml@gmail.com> writes:
> IMO, the problems are:

> 1) There is no way to get the hash value of an ExprState directly in
> SQL; therefore;
> 2) The only current way to test this is to create a test that checks
> for slow performance or a Hash Aggregate or Hash Join, and doing that
> is a terrible idea.

> For #1, it might be possible to do a test module with a function like
> CREATE FUNCTION test_hash_exprs(seed int4, strict bool, VARIADIC
> "any") which takes the variadic args and uses ExecBuildHash32Expr() to
> build an ExprState and returns the hashvalue / isnull result. I guess
> then we could run that on a table with a few columns and insert into a
> temp table, then run again with jit=1 and check the results match.
> Perhaps the function could even create two ExprStates, one with a
> faked-up PlanState to trick jit_compile_expr() into compiling the
> expression when there's a JIT provider. It could then return both
> values and allow the caller to compare them.

> I had an LLM draft most of that up locally. I'm just wondering if it's
> worth pursuing or not.

I'd argue not ...

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-17 23:49  David Rowley <dgrowleyml@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 10+ messages in thread

From: David Rowley @ 2026-11-17 23:49 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; Tor Erik Linnerud <tel@jklm.no>; pgsql-bugs@lists.postgresql.org

On Wed, 18 Nov 2026 at 12:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.com> writes:
> > I had an LLM draft most of that up locally. I'm just wondering if it's
> > worth pursuing or not.
>
> I'd argue not ...

I think I'm pretty much with you there. I didn't think it was worth
too many human cycles to write, and if that's true then offloading a
boatload of machine-generated code just adds future burden to humans.
A problem we're fighting on multiple fronts these days :-(

David





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
@ 2026-11-17 23:54  David Rowley <dgrowleyml@gmail.com>
  parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  1 sibling, 0 replies; 10+ messages in thread

From: David Rowley @ 2026-11-17 23:54 UTC (permalink / raw)
  To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: Tor Erik Linnerud <tel@jklm.no>; pgsql-bugs@lists.postgresql.org

On Tue, 17 Nov 2026 at 23:03, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
> The fix lgtm.  I tried Tor Erik's repro on master (LLVM 18) and it went
> from timing out (>2m) down to ~0.5s.

Thanks for checking. Pushed.

David






^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2026-11-17 23:54 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-16 11:26 PG 18 JIT regression: int4range hash breaks multi-key GROUP BY Tor Erik Linnerud <tel@jklm.no>
2026-11-16 16:29 ` Andres Freund <andres@anarazel.de>
2026-11-16 20:16   ` David Rowley <dgrowleyml@gmail.com>
2026-11-16 22:34 ` David Rowley <dgrowleyml@gmail.com>
2026-11-17 10:03   ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-11-17 14:49     ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-17 23:14       ` David Rowley <dgrowleyml@gmail.com>
2026-11-17 23:42         ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-17 23:49           ` David Rowley <dgrowleyml@gmail.com>
2026-11-17 23:54     ` David Rowley <dgrowleyml@gmail.com>

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