Postgresql – Handling performance problems with jit in postgres 12

jitpostgresqlpostgresql-12

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

Background:

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.

Best Answer

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.)