tl;dr: What is the best way to handle performance decrease caused by jit?


Lately I have migrated from postgres 11 to 12 and noticed, that some queries / procedures run considerably slower. I did some research and tested different configurations - it turned out, that the jit overhead exceeds its gains. I see two possible solutions:

  1. Disable jit permanently for all queries by running set jit = off; once. This helps, but does not feel natural. (Maybe because of changing the default configuration, which I did not much as there was no such need. The defaults are sane and there is low probability they need to be changed.) This would also mean throwing away possible performance gains when running long queries.

  2. Disabling jit at the beginning of the procedure and enabling it at the end. I have objections about the impact of changing the configuration on queries running in parallel. Of course this can harm performance of log queries, that would run alongside, but maybe this can cause other, possibly hard to detect bugs. Also this seems even less right than the first solution - changing the configuration as a part of the procedure.

The best way to handle this would be by fine-tuning the configuration, so that jit is not discarded in its entirety, but also configuration is not changed at runtime. Unfortunately I found little to no resource on how that would be possible (only parameters like jit_above_cost in the documentation that seem to be of little help).

Questions: What is the best way to solve this issue? Are there any criteria about when to disable jit except for running and measuring? Can the behavior be fine-tuned (for example jit disabled for specific queries)?

Edit: I'm attaching a plan with and without jit. The plans are too big to post here.

  • 2
    I have reverted back to jit = off completely (changing the default configuration). It seems JIT isn't there yet for everything. Apr 14 '20 at 12:01
  • You'd have to share EXPLAIN (ANALYZE, BUFFERS) output for some queries where performance becomes worse with and without JIT. Apr 14 '20 at 12:01
  • @LaurenzAlbe yes, I ran explain analyze using the same query on postgres 11 and 12 and the plans were exactly the same with respect to the note in ver. 12 about the time jit took, is this information helpful?
    – Andronicus
    Apr 14 '20 at 12:06
  • Yes, that data would be useful - please post the plans! Apr 14 '20 at 12:11
  • @Vérace the query is very complex, when I try to make it smaller, the difference in time i negligible. When pasting the whole plan here I'm getting "Body is limited to 30000 characters; you entered 259796."
    – Andronicus
    Apr 14 '20 at 12:24

In my opinion changing the default of jit to 'on' in v12 was a mistake, so based on that opinion turning it back off again seems pretty natural to me. I would have objected to the change if I had been paying more attention at the time (which doesn't mean I would have prevailed, of course). The change seems to harm at least as many people as it helps. And the people who were looking forward to the feature could surely be counted on to go change the default.

The defaults are sane and there is low probability they need to be changed

I think you have far too much respect for the defaults in general. They are basically set for the minimal credible machine size. Even the settings that were well chosen in general may not be well chosen for you.

This would also mean throwing away possible performance gains when running long queries.

Do you have example of such a query? Generally by the point the query is long-running enough that jit would make sense, it also starts touching enough data that it is no longer CPU bound and so that using jit or not is relatively unimportant (or it is missing an index, which the fixing of is far more important than jit). This is not universally true of course, and if you have an example you could then possibly use that to target the setting of jit_above_cost. (One problem with the jit_above* things is that they are based on total cost estimates, which are generally dominated by IO cost estimates. But choosing to use JIT or not based on estimated IO costs doesn't make sense. But the infrastructure isn't in place to separate out those types of cost estimates.)

  • this exactly solved my problem. One symptom I'd look for to see if this is your problem is if your long query is weirdly slow, and you can remove pieces of if (for example in the where clause) to make it faster, but it doesn't seem to matter which ones you remove - it just gets faster when it's shorter. Nov 10 '20 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.