Directed Joins in Snowflake

Hints have been a thing in database engines for years. I’ve always seen them as an absolute last resort, but have used them on other platforms. In general, Snowflake has not had customer configured hints. But like any optimizer, sometimes Snowflake does not choose the perfect join order. Snowflake recently introduced directed joins, which are similar to a hint. I don’t see directed joins as a performance feature, but more as a tool that can be used to explore performance problems.

What is a Directed Join?

A directed join is a new syntax option in Snowflake. It allows the user to specify the join order between two tables when they are being joined. The syntax looks something like this:

SELECT 
   ...
  FROM table_a 
    INNER DIRECTED JOIN table b ON ...
   ...
;

I wrote not long ago about flipped joins – how to identify them and how to address them. Directed joins are an awesome tool for exploring flipped joins and whether a different join order really would help a query perform better. Sometimes a join looks like a flipped join, but there’s a reason that makes sense for the flip from an optimization perspective.

By using a syntax that explicitly specifies the join order, we can confirm or dispel theories about what could make a query faster.

Example of a Directed Join

To demonstrate a flipped join, we have to get the Snowflake optimizer to choose an incorrect join order. This can be surprisingly difficult, but one thing that often leads to problems is when we apply functions to the join predicates. Avoiding functions on join keys is one tenet of good join performance. Here is a query that I used to try to induce a suboptimal join order, using SNOWFLAKE_SAMPLE_DATA:

USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF100;

SELECT 
    r.r_name AS region_name
    , n.n_name AS nation_name
    , s.s_name AS supplier_name
    , SUM(l.l_extendedprice * (1 - l.l_discount)) AS total_sales
    , COUNT(DISTINCT o.o_orderkey) AS order_count
  FROM orders AS o
    JOIN lineitem AS l
      ON HASH(o.o_orderkey) = HASH(l.l_orderkey)
    JOIN supplier AS s
      ON DIV0(s.s_suppkey, 1) = ROUND(l.l_suppkey / 1, 0)
    JOIN nation AS n
      ON CAST(n.n_nationkey AS NUMBER) = COALESCE(TO_NUMBER(s.s_nationkey), -1)
    JOIN region AS r
      ON MOD(n.n_regionkey, 10) = ABS(r.r_regionkey)
 WHERE YEAR(o.o_orderdate) = 1995
 GROUP BY 1, 2, 3
 ORDER BY total_sales DESC
;

The query profile for that looks like this:

We can see a flipped join here – Join [3] has 600 million rows on the left or build side, while it has only 22.75 million on the right hand side. This is more than an order of magnitude, and the join shows up as an expensive operator node. Because of the hash function used here, we see the optimizer chose the ORDERS table for the right hand side and the LINEITEM table for the left hand side, which is not the best choice.

Using a directed join to try to correct this, we can use:

SELECT 
    r.r_name AS region_name
    , n.n_name AS nation_name
    , s.s_name AS supplier_name
    , SUM(l.l_extendedprice * (1 - l.l_discount)) AS total_sales
    , COUNT(DISTINCT o.o_orderkey) AS order_count
  FROM orders AS o
    INNER DIRECTED JOIN lineitem AS l
      ON HASH(o.o_orderkey) = HASH(l.l_orderkey)
    JOIN supplier AS s
      ON DIV0(s.s_suppkey, 1) = ROUND(l.l_suppkey / 1, 0)
    JOIN nation AS n
      ON CAST(n.n_nationkey AS NUMBER) = COALESCE(TO_NUMBER(s.s_nationkey), -1)
    JOIN region AS r
      ON MOD(n.n_regionkey, 10) = ABS(r.r_regionkey)
 WHERE YEAR(o.o_orderdate) = 1995
 GROUP BY 1, 2, 3
 ORDER BY total_sales DESC
;

The query profile shows that this corrects the problem:

Not only does this plan look much better, but the time is better, too. When I ran these queries, the original query completed in 2 minutes and 9 seconds on an XS virtual warehouse, while the directed join query completed in just 32 seconds.

Notice that I have introduced the DIRECTED keyword in only one place here to tweak just that one join while allowing Snowflake to continue to choose the join order for the other joins. It is best not to define every join as a directed join, but only where we really have to enforce a particular join order.

Directed Join Pitfalls

As a performance expert, I’m a fan of having this option, but I’m also worried. Often in SQL, people copy and model code without understanding the implications of exactly what it means. I’m worried this will happen with DIRECTED.

Change Over Time

Even when we remove the variable of people using code without understanding the implications, I have other concerns with directed joins. Perhaps the top concern is that directed joins are very rigid, and the only constant thing when working with data is the constant change. Even if using a directed join today is amazing for performance, it is entirely possible that the balance of data changes over time and there comes a time in the future where it actually provides worse performance. Without using a directed join, the optimizer would likely notice this and change the plan as the data changes. If the directed join is specified, then that option is off the table.

Misuse of DIRECTED Keyword

There’s one sneaky error that can happen here. The join type keyword (INNER, LEFT, RIGHT, or FULL) must also be specified for a directed join. If it is not specified, the word “DIRECTED” may be interpreted silently as an alias and the desired effect might not occur. This is easy to demonstrate. Let’s say we did not alias the ORDERS table as ‘o’ in the query we’ve been working with. The alias is not really needed with the column naming in these tables. That might look like this:

SELECT 
    r.r_name AS region_name
    , n.n_name AS nation_name
    , s.s_name AS supplier_name
    , SUM(l.l_extendedprice * (1 - l.l_discount)) AS total_sales
    , COUNT(DISTINCT o_orderkey) AS order_count
  FROM orders
    DIRECTED JOIN lineitem l
      ON HASH(o_orderkey) = HASH(l.l_orderkey)
    JOIN supplier s
      ON DIV0(s.s_suppkey, 1) = ROUND(l.l_suppkey / 1, 0)
    JOIN nation n
      ON CAST(n.n_nationkey AS NUMBER) = COALESCE(TO_NUMBER(s.s_nationkey), -1)
    JOIN region r
      ON MOD(n.n_regionkey, 10) = ABS(r.r_regionkey)
 WHERE YEAR(o_orderdate) = 1995
 GROUP BY 1, 2, 3
 ORDER BY total_sales DESC
;

And the query plan that we get is the same flipped join as the original:

If we click on the table scan operator node for the ORDERS table, we can see in the Attributes information pane that the table has been aliased as “DIRECTED”:

There may only be a narrow set of circumstances where this can happen, but if directed joins aren’t delivering the guaranteed join order, this may be something to investigate.

Involved Syntax

The syntax for directed joins requires that the left table be listed first and the right table be listed second. There is no current way to reverse this. That means that if you have a fairly complicated set of joins, getting the table you want to be first can be a bit difficult.

It is also worth noting that this only works with explicit join syntax, and not implicit join syntax. You should be using explicit join syntax for other reasons, but it is worth noting for those attached to specifying their join conditions in the WHERE clause.

Summary

Directed joins are a useful tool, but should be used as a tool and a last resort, not as a first line of defense. It’s rare that one human’s understanding of the data today will continue to apply forever, and allowing the optimizer to change things as the data changes can be even more powerful. Making use of other tools to try to address a flipped join – such as using a CTE, clustering one or more of the tables to improve cardinality estimates, or asking Snowflake support for help may be better long-term solutions than using a directed join.