From: | Juan José Santamaría Flecha <juanjo(dot)santamaria(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: GEQO and join_collapse_limit correlation |
Date: | 2018-01-05 20:17:06 |
Message-ID: | CAC+AXB3ZaGLc=1qJgbWHGtguw4Nd9XgGeqEeP5-K0OSbeCNgGA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
> After some testing in order to lower the planning time we ended bringing
> > down the GEQO values, and we have the best results with:
>
> > from_collapse_limit = 150
> > join_collapse_limit = 150
> > geqo_threshold = 2
> > geqo_effort= 2
>
> Hmm. The trouble with this approach is that you're relying on GEQO
> to find a good plan, and that's only probabilistic --- especially so
> when you're reducing geqo_effort, meaning it doesn't try as many
> possibilities as it otherwise might. Basically, therefore, the
> fear is that every so often you'll get a bad plan.
>
What we felt odd was having to find a balance between geqo_threshold and
join_collapse_limit, lowering one was only effective after raising the
other. The geqo_effort was only mofidied after we found this path, and some
more testing.
In an environment with geqo_threshold=1 and join_collapse_limit=1, would
the planner be GEQO exclusive (and syntactic)?
If the queries are fairly stylized, you might be able to get good
> results by exploiting rather than bypassing join_collapse_limit:
> determine what a good join order is, and then write the FROM clause
> as an explicit JOIN nest in that order, and then *reduce* not raise
> join_collapse_limit to force the planner to follow the syntactic
> join order. In this way you'd get rid of most of the run-time
> join order search effort. Don't know how cooperative your ORM
> would be with such an approach though.
>
The ORM seems to build the join path just the other way round of what would
be good for the planner. The thing we should take a good look at if it is
really needed looking at +120 tables for a query that gets a pretty trivial
result, but that is completely off topic.
> regards, tom lane
>
Thanks for your repply.
Regards,
Juan José Santamaría
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-01-05 20:29:16 | Re: GEQO and join_collapse_limit correlation |
Previous Message | Tom Lane | 2018-01-05 16:16:17 | Re: GEQO and join_collapse_limit correlation |