Join Optimization

Apache Doris provides good out-of-the-box performance in most Join query scenarios. However, in scenarios with strict performance requirements, you can still optimize by arranging table colocation and guiding the optimizer with hints. It is recommended to first use Colocation Join to eliminate the network shuffle for bucketed equi-joins, and then, when the optimizer makes a suboptimal choice, fine-tune the shuffle method and Join order with Distribute Hint and Leading Hint.

Colocation

Colocation Join

Place data of related tables on the same BE node according to the same bucketing rules, so that equi-joins on bucketing columns can complete locally, completely eliminating cross-node data transfer.

Hint Tuning

Adjusting Join Shuffle Method with Hints

Use the [shuffle] and [broadcast] Distribute Hints to override the optimizer's choice of data distribution for the right table of a Join, and manually tune for specific scenarios.

Controlling Join Order with Leading Hint

When the Join order chosen by the CBO is suboptimal, use Leading Hint to manually specify the multi-table Join order, supporting various shapes such as left-deep tree, right-deep tree, and bushy tree.