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

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

От:
David Rowley <dgrowleyml@gmail.com>
Дата:
Вложения:
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

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


FAQ