Change default of jit to off
(forked off from "Add missing JIT inline pass for llvm>=17")
On Mon, 19 Jan 2026 at 11:53, Pierre Ducroquet <p.psql@pinaraf.info> wrote:
On Friday, January 16, 2026 10:29:59 AM Central European Standard Time Michael
Banck wrote:Hi,
On Thu, Jan 15, 2026 at 12:26:23PM +0100, Álvaro Herrera wrote:
On 2026-Jan-15, Andreas Karlsson wrote:
Great find! Sadly shows how little people actually use JIT.
I disagree. Given that JIT is enabled by default, I think lots of
people use it.Well, not sure about that - all of the three major hyperscalers disable
JIT in their managed Postgres offerings (or at least used to when I last
checked), and those are a major chunk of usage these days. Also, both
the RPM and (since recently) the Debian/Ubuntu community packages have
factored out the LLVM/jit part into their own packages and AFAIK they do
not get installed by default.So while the GUC is on by default, a lot of users might not use JIT
these days and not know either way.What they don't do, is realize that things are slower
than they could be -- much less try to figure out why.Right.
People have also seen blog articles saying «JIT is bad, switch it off» that are
right if you are in the wrong use cases for JIT. Which, to be fair, is not
easy to figure out.
+1 on disabling jit by default. At the FOSDEM Postgres developer meeting
consensus was hugely in favor of changing the default. So attached is a
trivial patch that does this.
Attachments:
v1-0001-Change-default-of-jit-to-off.patchtext/x-patch; charset=utf-8; name=v1-0001-Change-default-of-jit-to-off.patchDownload+5-5
On 30/01/2026 12:28, Jelte Fennema-Nio wrote:
+1 on disabling jit by default. At the FOSDEM Postgres developer meeting
consensus was hugely in favor of changing the default. So attached is a
trivial patch that does this.
+1 on changing the default.
It's bitten me on more than one occasion.
Best, Jim
On Fri, 2026-01-30 at 12:57 +0100, Jim Jones wrote:
On 30/01/2026 12:28, Jelte Fennema-Nio wrote:
+1 on disabling jit by default. At the FOSDEM Postgres developer meeting
consensus was hugely in favor of changing the default. So attached is a
trivial patch that does this.+1 on changing the default.
It's bitten me on more than one occasion.
+1, same here.
Yours,
Laurenz Albe
Am Freitag, dem 30.01.2026 um 12:28 +0100 schrieb Jelte Fennema-Nio:
People have also seen blog articles saying «JIT is bad, switch it
off» that are
right if you are in the wrong use cases for JIT. Which, to be fair,
is not
easy to figure out.+1 on disabling jit by default. At the FOSDEM Postgres developer
meeting
consensus was hugely in favor of changing the default. So attached is
a
trivial patch that does this.
+1, let's turn it off. I've seen many people hit by JIT and someone who
has the use case for it can easily turn it on again.
--
Thanks,
Bernd
Agree +1，Open it again if necessary.
On Fri, 30 Jan 2026 at 21:27, Bernd Helmle <mailings@oopsware.de> wrote:
Show quoted text
Am Freitag, dem 30.01.2026 um 12:28 +0100 schrieb Jelte Fennema-Nio:
People have also seen blog articles saying «JIT is bad, switch it
off» that are
right if you are in the wrong use cases for JIT. Which, to be fair,
is not
easy to figure out.+1 on disabling jit by default. At the FOSDEM Postgres developer
meeting
consensus was hugely in favor of changing the default. So attached is
a
trivial patch that does this.+1, let's turn it off. I've seen many people hit by JIT and someone who
has the use case for it can easily turn it on again.--
Thanks,
Bernd
On Fri, Jan 30, 2026, at 8:28 AM, Jelte Fennema-Nio wrote:
+1 on disabling jit by default. At the FOSDEM Postgres developer meeting
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 would 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]https://dba.stackexchange.com/a/264969 I
also think it was premature. It's been 7 years that JIT is enabled by default
and changing this parameter could surprise users who use JIT to mainly improve
runtime from OLAP queries. You can argue that the majority of the workloads are
not composed by analytic queries. Fine. That's not an excuse to perform poorly
with a small percentage of these analytic queries. Will you propose enabling it
again after fixing the majority of these performance reports? That will be
another source of confusion.
Since there are some reports exposing these issues and articles discussing 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 documentation
already stated the common issue -- short queries -- in the first paragraph [2]https://www.postgresql.org/docs/current/jit-decision.html.
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 columns
to pg_stat_statements that helps us understand if JIT is hurting your queries.
I don't have numbers but instead of turning JIT off we should adopt a less
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 granular
[3]: /messages/by-id/CAApHDvoq5VhV=2euyjgBN2bC8Bds9Dtr0bG7R=reeefJWKJRXA@mail.gmail.com
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 improvements
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.
[1]: https://dba.stackexchange.com/a/264969
[2]: https://www.postgresql.org/docs/current/jit-decision.html
[3]: /messages/by-id/CAApHDvoq5VhV=2euyjgBN2bC8Bds9Dtr0bG7R=reeefJWKJRXA@mail.gmail.com
--
Euler Taveira
EDB https://www.enterprisedb.com/
Le mardi 3 février 2026 à 1:42 AM, Euler Taveira <euler@eulerto.com> a écrit :
On Fri, Jan 30, 2026, at 8:28 AM, Jelte Fennema-Nio wrote:
+1 on disabling jit by default. At the FOSDEM Postgres developer meeting
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 would 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 default
and changing this parameter could surprise users who use JIT to mainly improve
runtime from OLAP queries. You can argue that the majority of the workloads are
not composed by analytic queries. Fine. That's not an excuse to perform poorly
with a small percentage of these analytic queries. Will you propose enabling it
again after fixing the majority of these performance reports? That will be
another source of confusion.Since there are some reports exposing these issues and articles discussing 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 documentation
already stated the common issue -- short queries -- in the first paragraph [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 columns
to pg_stat_statements that helps us understand if JIT is hurting your queries.
I don't have numbers but instead of turning JIT off we should adopt a less
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 granular
[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 improvements
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 OLTP, 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 default.
Here is my reasoning, feel free to contradict it... :)
1) costs in PostgreSQL is kind of bound to the number of tuples and the number of steps in the plan
2) LLVM compilation and optimization time is bound to the size of the generated 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 generated IR can get huge quickly, leading to surprisingly long compilation times. Without optimization, the generated machine code can behave worse than the 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 (mostly to get rid of pointless jumps around), I've not measured their full impact 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_above_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 used functions even with O0...
One issue that will always remain is that llvm is out of our control, it has an important maintenace cost and its compilation time can change a lot between releases. But it remains the best option at least for OLAP.
Re: Euler Taveira
+1 on disabling jit by default. At the FOSDEM Postgres developer meeting
consensus was hugely in favor of changing the default. So attached is a
trivial patch that does this.
I'm also in favor of disabling it by default. It's mostly hurting
people (or use cases) in OLTP workloads where it's just surprising
that JIT kicks in, while in OLAP workloads, people would usually know
they have a use for JIT and could enable it.
There are several layers of "default" here:
1) the default when compiling without extra options
2) the default when compiling with LLVM
3) the default in the deb/rpm/... packages with just "postgresql" installed
4) the default in the deb/rpm/... packages with the JIT package installed
5) the default that cloud providers set when using these/their own packages
The setting for 1) is off, so you might argue that for all other
cases, someone has made a conscious choice to enable it for the other
options, but for 3/5) and likely also for 4), that decision wasn't
made by the user actually using the database, so they don't have any
influence on it. In these cases, disabling it would remove the
"surprise" moment.
That leaves 2), but I would argue that all the cases should behave the
same. If different installs have different defaults, that's hard to
communicate.
You can argue that the majority of the workloads are
not composed by analytic queries. Fine. That's not an excuse to perform poorly
with a small percentage of these analytic queries.
It's neither an excuse to perform poorly on the OLTP queries that got
JITed when they should not have.
Since there are some reports exposing these issues and articles discussing 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 documentation
already stated the common issue -- short queries -- in the first paragraph [2].
Maybe we need to be more incisive.)
Documentation won't help here. This is always biting users by
surprise. Telling them that their non-analytic workload performs
poorly because they didn't read the JIT chapter when it shouldn't be
relevant to them is quite weird.
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 columns
to pg_stat_statements that helps us understand if JIT is hurting your queries.
I don't have numbers but instead of turning JIT off we should adopt a less
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 granular
[3] could restore confidence in it.
I've been pondering this as well, but I think we aren't there yet. It
will still make the compiler jump in in situations where it shouldn't
(maybe the cost estimates are just wildly off, but often the planner
still finds a good OLTP plan). We want predictability here.
Although I don't have plans to hack on JIT code but
reports may encourage a few developers to propose changes in this area.
We'd need people to actually work out the cost parameters. :)
Christoph
Import Notes
Reply to msg id not found: N0i6CrwXA0G3tzcW2jwtriaJTRMt_2KfMWkuakIKkZHS4Ncs-ha3jQPJcRZ94IlpmFITImACZB3DBxcOH5NWJyfK9ppog-aX9Wo1-cxNEeM=@pinaraf.info56a7e683-434f-4d2d-a7ed-9ddefcf426f4@app.fastmail.com
Hello
On 2/3/26 11:29 AM, Christoph Berg wrote:
5) the default that cloud providers set when using these/their own packages
Just to mention, it is disabled on most cloud provider :
* AWS RDS : off (but available)
* Google Cloud SQL : off (not supported)
https://docs.cloud.google.com/sql/docs/postgres/features#unsupported-features-for-postgres
* Microsoft Aure : off
https://learn.microsoft.com/fr-fr/azure/postgresql/server-parameters/param-query-tuning-planner-options?pivots=postgresql-18#jit
* Aiven : off https://aiven.io/docs/products/postgresql/howto/enable-jit
Given that so many places are already disabling it, +1 to disabling by
default - unless someone can come up with a costing tweak so it doesn't
fire when it shouldn't - but right now that seems something only humans can
truly determine.
Hi,
+1 for disabling it by default. Particularly with partitioning having become
much more common since jit compilation was added, combined with LLVM getting a
lot slower over time, that's unfortunately the right call until some
substantial improvements are made.
On 2026-02-03 12:03:51 -0500, Greg Sabino Mullane wrote:
Given that so many places are already disabling it, +1 to disabling by
default - unless someone can come up with a costing tweak so it doesn't
fire when it shouldn't - but right now that seems something only humans can
truly determine.
I think it needs more than a costing tweak. The most important thing would be
to get caching (there's progress, albeit very slow one, towards that), so the
downside of JIT compilation doesn't hit you over and over.
Relatedly, we often end up with the almost-same expression being jit compiled
many times in partitioned workloads, which is one of the main sources of high
jit compilation times.
We also need to just increase the benefit of JIT compilation further, the code
we generate leaves a *lot* on the table, particularly for more complicated
expressions, where the gain also can be the biggest.
Greetings,
Andres Freund
On Tue, 3 Feb 2026 at 18:13, Andres Freund <andres@anarazel.de> wrote:
+1 for disabling it by default. Particularly with partitioning having become
much more common since jit compilation was added, combined with LLVM getting a
lot slower over time, that's unfortunately the right call until some
substantial improvements are made.
I'm marking this as ready for committer.
On 2/13/26 15:57, Jelte Fennema-Nio wrote:
On Tue, 3 Feb 2026 at 18:13, Andres Freund <andres@anarazel.de> wrote:
+1 for disabling it by default. Particularly with partitioning having become
much more common since jit compilation was added, combined with LLVM getting a
lot slower over time, that's unfortunately the right call until some
substantial improvements are made.I'm marking this as ready for committer.
ISTM there's a clear consensus to get this committed for PG19, so
barring objections I'll take care of that in the next couple days.
Unless someone else wants to ...
Another option would be to leave that for mid-beta, which is where we
tweaked the io_method GUCs last year. But we did that to get some
testing for 'worker' (in case we revert to 'sync'), and we don't need
that for jit.
regards
--
Tomas Vondra
Tomas Vondra <tomas@vondra.me> writes:
ISTM there's a clear consensus to get this committed for PG19, so
barring objections I'll take care of that in the next couple days.
Unless someone else wants to ...
+1
Another option would be to leave that for mid-beta, which is where we
tweaked the io_method GUCs last year. But we did that to get some
testing for 'worker' (in case we revert to 'sync'), and we don't need
that for jit.
Doesn't seem like something to change mid-beta. If it makes anyone
unhappy, we'd best find out sooner not later.
regards, tom lane
Le vendredi 20 mars 2026 à 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Tomas Vondra <tomas@vondra.me> writes:
ISTM there's a clear consensus to get this committed for PG19, so
barring objections I'll take care of that in the next couple days.
Unless someone else wants to ...+1
Another option would be to leave that for mid-beta, which is where we
tweaked the io_method GUCs last year. But we did that to get some
testing for 'worker' (in case we revert to 'sync'), and we don't need
that for jit.Doesn't seem like something to change mid-beta. If it makes anyone
unhappy, we'd best find out sooner not later.
I've not seen any feedback on my "counter"-proposal: switch jit_tuple_deforming to off by default. Sure, for the perfect llvmjit use cases this will reduce the performance benefits, but it will remove most if not all the problematic queries (for instance queries running on many partitions, adding/moving columns leading to explosions in compilation time...)
Of course if there are other troublesome situations, I would love being proven wrong.
On Mon Mar 23, 2026 at 9:50 AM CET, Jelte Fennema-Nio wrote:
I think Andres' reply also applied to that suggestion. Partitioning
really messes up jit for expressions too. I was able to pretty easily
create some bad cases even with jit_tuple_deforming set to off. A
query involving many partitions, but with relatively few rows per
partition, jit became ~54x slower when using -O2 (743ms vs 40318ms).
And even with -O0, jit is still ~3x slower (221ms vs 711ms). Full
script and results attached.
Resending the above with a compressed attachment, because it was too
large for the mailing list.
Import Notes
Reply to msg id not found: CAGECzQSc_AbvhKU=BOc6tAAqU5Bsu9EFOirDR4_O=fio1_XYSA@mail.gmail.com
Hi,
On 2026-03-23 06:33:33 +0000, Pierre Ducroquet wrote:
Le vendredi 20 mars 2026 à 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Tomas Vondra <tomas@vondra.me> writes:
ISTM there's a clear consensus to get this committed for PG19, so
barring objections I'll take care of that in the next couple days.
Unless someone else wants to ...+1
Another option would be to leave that for mid-beta, which is where we
tweaked the io_method GUCs last year. But we did that to get some
testing for 'worker' (in case we revert to 'sync'), and we don't need
that for jit.Doesn't seem like something to change mid-beta. If it makes anyone
unhappy, we'd best find out sooner not later.I've not seen any feedback on my "counter"-proposal: switch
jit_tuple_deforming to off by default. Sure, for the perfect llvmjit use
cases this will reduce the performance benefits, but it will remove most if
not all the problematic queries (for instance queries running on many
partitions, adding/moving columns leading to explosions in compilation
time...) Of course if there are other troublesome situations, I would love
being proven wrong.
I doubt that that addresses the problem in any meaningful way. In nearly all
the cases I've looked at expression compilation completely dominates the cost,
due to being instantiated for every partition etc. So I'm rather surprised to
see this claim?
We should add the function deduplication pass for O0, to reduce the cost of
tuple deforming when accessing many partitions, but in my measurements that
isn't the critical path right now.
Greetings,
Andres Freund
On 3/24/26 22:56, Andres Freund wrote:
Hi,
On 2026-03-23 06:33:33 +0000, Pierre Ducroquet wrote:
Le vendredi 20 mars 2026 à 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Tomas Vondra <tomas@vondra.me> writes:
ISTM there's a clear consensus to get this committed for PG19, so
barring objections I'll take care of that in the next couple days.
Unless someone else wants to ...+1
Another option would be to leave that for mid-beta, which is where we
tweaked the io_method GUCs last year. But we did that to get some
testing for 'worker' (in case we revert to 'sync'), and we don't need
that for jit.Doesn't seem like something to change mid-beta. If it makes anyone
unhappy, we'd best find out sooner not later.I've not seen any feedback on my "counter"-proposal: switch
jit_tuple_deforming to off by default. Sure, for the perfect llvmjit use
cases this will reduce the performance benefits, but it will remove most if
not all the problematic queries (for instance queries running on many
partitions, adding/moving columns leading to explosions in compilation
time...) Of course if there are other troublesome situations, I would love
being proven wrong.I doubt that that addresses the problem in any meaningful way. In nearly all
the cases I've looked at expression compilation completely dominates the cost,
due to being instantiated for every partition etc. So I'm rather surprised to
see this claim?
That's my understanding too. I did search in the archives for
performance issues related to JIT, and those are the first two I found:
1)
/messages/by-id/6191e1ee-d548-2a8c-d87d-dab63c05ad2e@code-sourcery.de
2)
/messages/by-id/LEXPR01MB09897821BA2CDDB70225EF7BE79C0@LEXPR01MB0989.DEUPRD01.PROD.OUTLOOK.DE
Both have all the JIT stuff enabled, and the timings are dominated by
Optimization:
* Timing: Generation 15.707 ms, Inlining 4.688 ms, Optimization
652.021 ms, Emission 939.556 ms, Total 1611.973 ms
* Timing: Generation 27.833 ms, Inlining 117.093 ms, Optimization
3917.001 ms, Emission 2027.251 ms, Total 6089.177 ms
Or am I mistaken and it could still be due to deforming? It'd be a bit
silly to disable jit_tuple_deforming=off, only to find it's not enough
and have to disable more stuff for PG20.
Furthermore, jit_tuple_deforming is marked as DEVELOPER_OPTIONS, and is
not in the sample. Wouldn't that be a bit confusing/surprising, if we
disable just that, as it's not in the sample?
regards
--
Tomas Vondra
Pushed, after rewording the commit message a little bit, and removing
the sentence in the docs saying
(before PostgreSQL 19 the default was on)
We've changed the defaults for a number of GUCs over the years (and some
of the GUCs were more critical than "jit"), and we have not added
similar comments to those. I haven't found a single GUC with such
comment, actually. So why have it for "jit"?
regards
--
Tomas Vondra
