Since JIT is on by default in v12, I wanted to revisit the issue raised in
https://www.postgresql.org/message-id/CAMkU=1zVhQ5k5d=YyHNyrigLUNTkOj4=YB17s9--3ts8H-SO=Q@mail.gmail.com
When the total estimated cost is between jit_above_cost and jit_optimize_above_cost, I get a substantial regression in the attached. Note that I did not devise this case specifically to cause this problem, I just stumbled upon it.
JIT, no optimization: 10.5s
JIT, optimization: 3.8s
no JIT: 4.1s
It seems like the unoptimized JIT code is much worse than the general purpose code.
This is on AWS c4.large, Ubuntu 18.04, installed from PGDG apt repository. No config changes were made, other than the local ones included in the script. (Previously there were questions about how LLVM was configured, that I couldn't really answer well, but here there should be question as I didn't compile or configure it at all.)
There were some proposed mitigations in sister threads, but none have been adopted in v12.
I think it is intuitive, and with empirical evidence, that we do not want to JIT compile at all unless we are going to optimize the compiled code.
Is there a rationale for, or other examples to show, that it makes sense for the default value of jit_optimize_above_cost to be 5 fold higher than the default setting of jit_above_cost?
I think these defaults are setting a trap for our users who aren't really interested in JIT, and are just upgrading to stay on the most-current version. I would propose lowering the default jit_optimize_above_cost to be the same as jit_above_cost, or set it to 0 so that jit_above_cost is always in control and always optimizes.
Cheers,
Jeff