Re: bad JIT decision

On Sat, 25 Jul 2020 at 08:46, Scott Ribe <scott_ribe(at)elevated-dev(dot)com> wrote:
> Given the magnitude of the miss in using JIT here, I am wondering: is it possible that the planner does not properly take into account the cost of JIT'ing a function for multiple partitions? Or is it that the planner doesn't have enough info about the restrictiveness of conditions, and is therefore anticipating running the functions against a great many rows?

It does not really take into account the cost of jitting. If the total
plan cost is above the jit threshold then jit is enabled. If not, then
it's disabled.

There are various levels of jit and various thresholds that can be tweaked, see:

select name,setting from pg_settings where name like '%jit%';

But as far as each threshold goes, you either reach it or you don't.
Maybe that can be made better by considering jit in a more cost-based
way rather than by threshold, that way it might be possible to
consider jit per plan node rather than on the query as a whole. e.g,
if you have 1000 partitions and 999 of them have 1 row and the final
one has 1 billion rows, then it's likely a waste of time to jit
expressions for the 999 partitions.

However, for now, you might just want to try raising various jit
thresholds so that it only is enabled for more expensive plans.

David