Hi, Join order planning is an important optimization problem. The widely-used heuristic is to consider all bushy trees without cross-joins. There is proof [1] that a pair of commute and associate rules is a complete ruleset to explore all bushy trees without the cross-joins if the initial join tree has no cross-joins.
In Apache Calcite, we do not suppress cross-products. As a result, even simple join topologies, like a chain, cannot be planned in a reasonable time for more than 6-7 tables. How do you feel if we add an optional cross-join suppression to the JoinAssociateRule, and possibly JoinPushThroughJoinRule? The cross-join suppression is not the only problem with the exhaustive join planning in Apache Calcite. But at the very least, it is simple to implement, and it extends the number of tables in a join that could be planned exhaustively for some topologies by additional 2-5 tables. Regards, Vladimir. [1] https://dl.acm.org/doi/10.14778/2732977.2732997
