PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
Tor Erik Linnerud <tel@jklm.no>
Дата:
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
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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
Andres Freund <andres@anarazel.de>
Дата:
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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
David Rowley <dgrowleyml@gmail.com>
Дата:
On Tue, 17 Nov 2026 at 05:29, Andres Freund 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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
On Tue, 17 Nov 2026 at 00:27, Tor Erik Linnerud 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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Дата:
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
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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Ayush Tiwari 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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
David Rowley <dgrowleyml@gmail.com>
Дата:
On Wed, 18 Nov 2026 at 03:49, Tom Lane wrote: > > Ayush Tiwari 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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
David Rowley 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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
David Rowley <dgrowleyml@gmail.com>
Дата:
On Wed, 18 Nov 2026 at 12:42, Tom Lane wrote: > > David Rowley 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
Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY
От:
David Rowley <dgrowleyml@gmail.com>
Дата:
On Tue, 17 Nov 2026 at 23:03, Ayush Tiwari 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