=?UTF-8?Q?Juan_Jos=C3=A9_Santamar=C3=ADa_Flecha?= <juanjo.santamaria@xxxxxxxxx> writes: > We recently had an issue in production. We have queries that are > procedurally generated by an Object/Relational Mapping framework. Some of > these queries are huge, involving over 120 tables. Yeah, you're going to have problems with that :-( > 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. 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. regards, tom lane
