PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

Started by Tor Erik Linnerud6 days ago10 messagesbugs
Jump to latest

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

#2Andres Freund
andres@anarazel.de
In reply to: Tor Erik Linnerud (#1)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

#3David Rowley
dgrowleyml@gmail.com
In reply to: Andres Freund (#2)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

#4David Rowley
dgrowleyml@gmail.com
In reply to: Tor Erik Linnerud (#1)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

Attachments:

t259239_4
v1-0001-Resolve-mismatch-between-JIT-and-non-JIT-hashing.patchapplication/octet-stream; name=v1-0001-Resolve-mismatch-between-JIT-and-non-JIT-hashing.patchDownload+25-17
#5Ayush Tiwari
ayushtiwari.slg01@gmail.com
In reply to: David Rowley (#4)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ayush Tiwari (#5)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

#7David Rowley
dgrowleyml@gmail.com
In reply to: Tom Lane (#6)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

#8Tom Lane
tgl@sss.pgh.pa.us
In reply to: David Rowley (#7)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

#9David Rowley
dgrowleyml@gmail.com
In reply to: Tom Lane (#8)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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

#10David Rowley
dgrowleyml@gmail.com
In reply to: Ayush Tiwari (#5)
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY

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