Handling performance problems with jit in postgres 12
17
votes
1
answer
13944
views
**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.
Asked by Andronicus
(271 rep)
Apr 14, 2020, 11:38 AM
Last activity: Apr 14, 2020, 03:29 PM
Last activity: Apr 14, 2020, 03:29 PM