On Wed, 30 Nov 2022 at 03:31, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > IMO it was a mistake to turn JIT on in the default config, so that's one > > thing you'll likely want to change. > > I wouldn't necessarily go quite that far, but I do think that the > default cost thresholds for invoking it are enormously too low, > or else there are serious bugs in the cost-estimation algorithms > for deciding when to use it. A nearby example[1] of a sub-1-sec > partitioned query that took 30sec after JIT was enabled makes me > wonder if we're accounting correctly for per-partition JIT costs.
I'm very grateful for JIT. However, I do agree that the costs need to work.
The problem is that the threshold to turn JIT on does not consider how many expressions need to be compiled. It's quite different to JIT compile a simple one-node plan with a total cost of 100000 than to JIT compile a plan that costs the same but queries 1000 partitions. I think we should be compiling expressions based on the cost of the individial node rather than the total cost of the plan.
I think a big win for JIT would be to be able to do it just once per cached plan, not once per execution. And then have it turned on only for prepared statements. Of course that means JIT couldn't do parameter folding, but I don't know if it does that anyway. Also, very expensive plans are generally dominated by IO cost estimates, and I think it doesn't make sense to drive JIT decisions based predominantly on the expected cost of the IO. If the planner separated IO cost estimate totals from CPU cost estimate totals, it might open up better choices.