​
Background: What is join order optimization?

Join order optimization improves the performance of join queries. Usually, we want the smaller table to be on the build side of the join. For this, Firebolt estimates the row counts of the inputs and orders the joins accordingly. For queries joining more than two tables, join order optimization also determines which of the tables should be joined first. You can view the join order in the explain output, with the second input of a join operator being its build side.

​
Controlling join orders in Firebolt

By default, Firebolt’s query planner optimizes the join order based on estimating the row counts of different subresults. However, there are scenarios where you may want to overwrite this because you have better manual estimates. The no_join_ordering hint gives you explicit control over the planner’s behavior. If you specify this hint, the planner will not optimize the join order and instead use the order that is given in the query. The hint provides more granular control than optimizer_mode = 'user_guided'.

​
Example

As an example, assume that we have tables t1 with 100 rows and t2 with 10000 rows. By default, Firebolt uses t1 as the build side (listed second in the explain output), even if the query specifies t1 first. With the no_join_ordering hint, Firebolt uses the order as specified in the query. 
explain(logical)
select * from t1 join t2 on t1.a = t2.a;