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
