Re: Stampede of the JIT compilers

On Sat, Jun 24, 2023 at 8:14 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Sun, 25 Jun 2023 at 05:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > James Coleman <jtc331(at)gmail(dot)com> writes:
> > > On Sat, Jun 24, 2023 at 7:40 AM Tomas Vondra
> > > <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> > >> On 6/24/23 02:33, David Rowley wrote:
> > >>> On Sat, 24 Jun 2023 at 02:28, James Coleman <jtc331(at)gmail(dot)com> wrote:
> > >>>> There are a couple of issues here. I'm sure it's been discussed
> > >>>> before, and it's not the point of my thread, but I can't help but note
> > >>>> that the default value of jit_above_cost of 100000 seems absurdly low.
> > >>>> On good hardware like we have even well-planned queries with costs
> > >>>> well above that won't be taking as long as JIT compilation does.
> >
> > >>> It would be good to know your evidence for thinking it's too low.
> >
> > > It's definitely possible that I stated this much more emphatically
> > > than I should have -- it was coming out of my frustration with this
> > > situation after all.
> >
> > 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.
>
> 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.

I did say in the original email "encountered a situation where a
misplanned query (analyzing helped with this, but I think the issue is
still relevant)".

I'll look at specifics again on Monday, but what I do remember is that
there were a lot of joins, and we already know we have cases where
those are planned poorly too (even absent bad stats).

What I wanted to get at more broadly here was thinking along the lines
of how to prevent the misplanning from causing such a disaster.

Regards,
James Coleman