Re: Stampede of the JIT compilers

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> I've seen plenty of other reports and I do agree there is a problem,
> but I think you're jumping to conclusions in this particular case.
> I've seen nothing here that couldn't equally indicate the planner
> didn't overestimate the costs or some row estimate for the given
> query. The solution to those problems shouldn't be bumping up the
> default JIT thresholds it could be to fix the costs or tune/add
> statistics to get better row estimates.
> I don't think it's too big an ask to see a few more details so that we
> can confirm what the actual problem is.

Okay, I re-did the regression tests with log_min_duration_statement set to
zero, and then collected the reported runtimes. (This time, the builds
also had --enable-cassert turned off, unlike my quick check yesterday.)
I attach the results for anyone interested in doing their own analysis,
but my preliminary impression is:

(1) There is *no* command in the core regression tests where it makes
sense to invoke JIT. This is unsurprising really, because we don't
allow any long-running queries there. The places where the time
with LLVM beats the time without LLVM look to be noise. (I didn't
go so far as to average the results from several runs, but perhaps
someone else would wish to.)

(2) Nonetheless, we clearly do invoke JIT in some places, and it adds
as much as a couple hundred ms to what had been a query requiring a few
ms. I've investigated several of the ones with the worst time penalties,
and they indeed look to be estimation errors. The planner is guessing
that a join for which it lacks any stats will produce some tens of
thousands of rows, which it doesn't really, but that's enough to persuade
it to apply JIT.

(3) I still think this is evidence that the cost thresholds are too low,
because even if these joins actually did produce some tens of thousands
of rows, I think we'd be well shy of breakeven to use JIT. We'd have
to do some more invasive testing to prove that guess, of course. But
it looks to me like the current situation is effectively biased towards
using JIT when we're in the gray zone, and we'd be better off reversing
that bias.

regards, tom lane