Date: Tue, 03 Feb 2026 08:42:33 +0000
To: Euler Taveira <euler@eulerto.com>
From: Pierre Ducroquet <p.psql@pinaraf.info>
Cc: Jelte Fennema <postgres@jeltef.nl>, =?utf-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Andreas Karlsson <andreas@proxel.se>, Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Michael Banck <mbanck@gmx.net>, Christoph Berg <myon@debian.org>
Subject: Re: Change default of jit to off
Message-ID: <N0i6CrwXA0G3tzcW2jwtriaJTRMt_2KfMWkuakIKkZHS4Ncs-ha3jQPJcRZ94IlpmFITImACZB3DBxcOH5NWJyfK9ppog-aX9Wo1-cxNEeM=@pinaraf.info>
In-Reply-To: <56a7e683-434f-4d2d-a7ed-9ddefcf426f4@app.fastmail.com>
References: <DG1VZJEX1AQH.2EH4OKGRUDB71@jeltef.nl> <56a7e683-434f-4d2d-a7ed-9ddefcf426f4@app.fastmail.com>
Feedback-ID: 175348558:user:proton
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/N0i6CrwXA0G3tzcW2jwtriaJTRMt_2KfMWkuakIKkZHS4Ncs-ha3jQPJcRZ94IlpmFITImACZB3DBxcOH5NWJyfK9ppog-aX9Wo1-cxNEeM%3D%40pinaraf.info>
Precedence: bulk

Le mardi 3 f=C3=A9vrier 2026 =C3=A0 1:42 AM, Euler Taveira <euler@eulerto.c=
om> a =C3=A9crit=C2=A0:

> On Fri, Jan 30, 2026, at 8:28 AM, Jelte Fennema-Nio wrote:
>
> > +1 on disabling jit by default. At the FOSDEM Postgres developer meetin=
g
> > consensus was hugely in favor of changing the default. So attached is a
> > trivial patch that does this.
>
>
> As someone that analyzed dozens of cases that JIT performs poorly, I woul=
d say
> +1. However, I don't think it is a good idea. JIT was introduced in v11
> (defaults to off) and the default was changed to on for v12. As Jeff said=
 [1] I
> also think it was premature. It's been 7 years that JIT is enabled by def=
ault
> and changing this parameter could surprise users who use JIT to mainly im=
prove
> runtime from OLAP queries. You can argue that the majority of the workloa=
ds are
> not composed by analytic queries. Fine. That's not an excuse to perform p=
oorly
> with a small percentage of these analytic queries. Will you propose enabl=
ing it
> again after fixing the majority of these performance reports? That will b=
e
> another source of confusion.
>
> Since there are some reports exposing these issues and articles discussin=
g it,
> I would suggest improving the documentation stating that there are issues=
 in
> this area and that they will be resolved in the future. (The documentatio=
n
> already stated the common issue -- short queries -- in the first paragrap=
h [2].
> Maybe we need to be more incisive.)
>
> Instead, of using the argument that JIT hurts short queries, I would like=
 to
> see other alternative solutions for this JIT issue. The v15 added some co=
lumns
> to pg_stat_statements that helps us understand if JIT is hurting your que=
ries.
> I don't have numbers but instead of turning JIT off we should adopt a les=
s
> invasive solution like increasing the thresholds that triggers it --
> jit_*_above_cost (probably using some pg_stat_statements reports). It is =
not a
> definitive fix, JIT needs improvement and an idea like making JIT more gr=
anular
> [3] could restore confidence in it.
>
> Maybe I'm being too optimistic but this is a similar argument that kept
> optimizer hints out of core: hints can reduce the pace of planner improve=
ments
> since fewer bad query plans are reported due to use of hints to force the
> optimal query plan. Although I don't have plans to hack on JIT code but
> reports may encourage a few developers to propose changes in this area.

Well, I can't say I disagree with the feeling here.
llvmjit is good or even great for OLAP, depending on your needs. But for OL=
TP, it falls short from delivering.
I think that disabling jit completely would be an overkill workaround, and =
would argue instead that jit_tuple_deforming should be turned off by defaul=
t.
Here is my reasoning, feel free to contradict it... :)

1) costs in PostgreSQL is kind of bound to the number of tuples and the num=
ber of steps in the plan
2) LLVM compilation and optimization time is bound to the size of the gener=
ated code
3) JIT speed benefits are bound to the number of time the code is executed

So far so good. But what is troublesome is tuple deforming. It is bound to =
a parameter, the number, type and layout of attributes to deform. The gener=
ated IR can get huge quickly, leading to surprisingly long compilation time=
s. Without optimization, the generated machine code can behave worse than t=
he heavily optimized tuple deforming we have when interpreting.

My stake here is that we should either:
- add a jit_deform_above_cost setting set to a high value, higher than jit_=
optimize_above_cost
- or switch by default jit_tuple_deforming to off

Switching jit to off seems overkill compared to this.

I have pending patches to improve the generated deforming code with O0 (mos=
tly to get rid of pointless jumps around), I've not measured their full imp=
act yet but they should arrive here by the end of the week.
I would love being proved wrong here. If you can send me some queries where=
 llvmjit is slower even without tuple deforming when below jit_optimize_abo=
ve_cost, I'd happily work on these to turn things around. Note that I also =
have sent a first patch to speed up the generated code for many commonly us=
ed functions even with O0...

One issue that will always remain is that llvm is out of our control, it ha=
s an important maintenace cost and its compilation time can change a lot be=
tween releases. But it remains the best option at least for OLAP.