> 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.


Juan José Santamaría

Reply via email to