Re: Stampede of the JIT compilers

Hi,

On 2023-06-24 13:54:53 -0400, Tom Lane wrote:
> I think there is *plenty* of evidence that it is too low, or at least
> that for some reason we are too willing to invoke JIT when the result
> is to make the overall cost of a query far higher than it is without.
> Just see all the complaints on the mailing lists that have been
> resolved by advice to turn off JIT. You do not even have to look
> further than our own regression tests: on my machine with current
> HEAD, "time make installcheck-parallel" reports
>
> real 0m8.544s
> user 0m0.906s
> sys 0m0.863s
>
> for a build without --with-llvm, and
>
> real 0m13.211s
> user 0m0.917s
> sys 0m0.811s

IIRC those are all, or nearly all, cases where we have no stats and the plans
have ridiculous costs (and other reasons like enable_seqscans = false and
using seqscans nonetheless). In those cases no cost based approach will work
:(.

> I don't know whether raising the default would be enough to fix that
> in a nice way, and I certainly don't pretend to have a specific value
> to offer. But it's undeniable that we have a serious problem here,
> to the point where JIT is a net negative for quite a few people.

Yea, I think at the moment it's not working well enough to be worth having on
by default. Some of that is due to partitioning having become much more
common, leading to much bigger plan trees, some of it is just old stuff that I
had hoped could be addressed more easily.

FWIW, Daniel Gustafsson is hacking on an old patch of mine that was working
towards making the JIT result cacheable (and providing noticeably bigger
performance gains).

> > In that context capping the number of backends compiling, particularly
> > where plans (and JIT?) might be cached, could well save us (depending
> > on workload).
>
> TBH I do not find this proposal attractive in the least.

Yea, me neither. It doesn't address any of the actual problems and will add
new contention.

Greetings,

Andres Freund