Tuning Guide for Joins in SQL Queries

[2010YOUY01]

Is your feature request related to a problem or challenge?

It would be great to add an example under datafusion-examples to illustrate the following:

1. Default Planning and Optimization Strategy

   • What is the default strategy for planning and optimizing queries with joins: How does the engine decide the physical join type (e.g., sort-merge join vs. hash join)?
   • How is the join order determined?
     Introducing the high-level internal optimization strategy can help users better understand and perform their own optimizations.

2. Example Queries and EXPLAIN Output

   • Provide example queries and show how the default policies optimize them.
   • Explain how to interpret the output of the EXPLAIN statement.

3. Relevant Configuration Settings
   Include explanation for the related configurations (note that some settings might not yet be supported in DF):

   • If multiple physical join types are applicable, how can a user disable or prefer a specific join type?
     (e.g., for a simple inner join with equality conditions, how to force a Nested Loop Join in the execution plan)
   • How to override the join order?
     (e.g., the optimizer chooses t1, t3, t2 but the user prefers t1, t2, t3)
   • Any other relevant configuration options.

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response