On Tue, May 01, 2012 at 04:34:10PM +0200, Clemens Eisserer wrote:
> select ..... from table1 ... left outer join table 15 .... WHERE
> table1.id IN (select id .... join table16 ... join table20 WHERE
> table20.somevalue=?)
>
> Starting with some amount of joins, the optimizer starts to do quite
> suboptimal things like hash-joining huge tables where selctivity would
> very low.
> I already raised join_collapse_limit and from_collapse_limit, but
> after a certain point query planning starts to become very expensive.
On Sun, May 13, 2012 at 04:35:30PM +0200, Clemens Eisserer wrote:
> > Since you have 15+ tables at the top level, the genetic query optimizer should
> > be kicking in and delivering a plan in reasonable time, albeit with plan
> > quality hazards. ??There's a danger zone when the deterministic planner is
> > still in effect but {from,join}_collapse_limit have limited the scope of its
> > investigation. ??If you're in that zone and have not hand-tailored your
> > explicit join order, poor plans are unsurprising. ??What exact configuration
> > changes are you using?
>
> Basically only the changes, suggested here a year ago, which made the
> problem go away for less complex queries:
>
> geqo_threshold = 20
> from_collapse_limit = 13
> join_collapse_limit = 13
Given those settings and the query above, the planner will break the 15
top-level tables into lists of 13 and 2 tables, the 20 subquery tables into
lists of 13 and 7 tables. The split points arise from order of appearance in
the query text. The planner then optimizes join order within each list but
not across lists. That perfectly explains your observation of "hash-joining
huge tables where selctivity would very low".
If it were me, I would try two things. First, set from_collapse_limit = 100,
join_collapse_limit = 100, geqo_threshold = 8. This will let the planner
consider all join orders for the 35 tables; it will use the genetic query
optimizer to choose one. See if the plan time and resulting plan are decent.
Second, set from_collapse_limit = 100, join_collapse_limit = 100, geqo = off
and EXPLAIN the query. This will take forever and might exhaust all system
memory. If it does complete, you'll see the standard planner's opinion of an
optimal join order. You can then modify the textual join order in your query
to get the same plan despite returning to a lower join_collapse_limit. Since
Hibernate is generating your queries, that may prove inconvenient. It's the
remaining escape hatch if the genetic query optimizer does not suffice.
