PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
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
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 sGROUP BY p, b, r (range last):
17.12: 0.45 s
18.6: 0.39 sWith 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
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
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:
--- 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
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]: https://llvm.org/docs/LangRef.html#trunc-to-instruction
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
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
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
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
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