There are some things about joins in Snowflake that it is interesting to know and to recognize in a query profile. I thought I’d cover a couple of those here.
Hash Joins
In Problematic Query Patterns in Snowflake: Join Explosion, I revealed that Snowflake uses hash joins to fulfill its joins. For more information on what a hash join is, including a gif that I am inordinately proud of, please go read the opening of that article.
In short, a hash join involves building a hash table of join key values for one side of the join, and then probing them from the other side of the join to find out if matches exist. The two sides of the join are referred to as the build side and the probe side of the join. There is more effort and more memory required for the build side of the join.
Flipped Joins
Since the build side of a hash join requires more memory and more compute, we want the smaller number of rows to be on the build side, while the larger cardinality should be on the probe side. With few exceptions, Snowflake represents the build side on the left and the probe side on the right. This means that when we’re looking at a Snowflake query profile, we want the lower row count to be on the left and the higher row count on the right. If you work with Snowflake and remember nothing else from this article, remember the mnemonic LOWER LEFT. Lower row count should be on the left.
At small scale getting the join order flipped doesn’t make much difference, but as data scales up and query complexity increases, this can become both more frequent and become a larger problem. Because the build side requires more memory, a flipped join can cause spilling within the warehouse, or even to remote storage. Spilling to remote storage is often a performance killer.
This can cause query performance to drastically slow down. Sometimes the slower performance can be combatted by using a larger warehouse. Flipped joins can cause queries to not only be slower, but more expensive as well.
Not every flipped join is a cause for concern. Especially at the small end of the scale, the difference of a flipped join may not even be noticeable. There can also be specific reasons why a flipped join is useful, particularly when the difference is not orders of magnitude and there is ample memory in the warehouse compared to the data size.
Flipped joins may be a problem worth exploring when the join operator is using the lion’s share of the CPU time for the query in the query profile, or when there is a sudden worsening of performance of the same query given similar data size. Flipped joins are also usually only a concern if the difference in cardinality between the two sides is an order of magnitude or larger.
Cause of Flipped Joins
What causes problematic flipped joins? There are two primary causes.
The first main cause of a flipped join is query complexity. It is staggering the rate at which the join order possibilities escalates. For example, if we have 4 tables, we have 24 possible join orders(4 factorial). But if we have 10 tables, we have 3,628,800 possible join orders(10 factorial). Considering the optimal join order takes time. That time is part of the total query execution time. There are limits on the time and resources put into considering join orders, and for complex queries, the time and resources may not be available to get to the perfect join order. It is therefore more likely to see join order issues in more complex queries.
The second main cause of a flipped join is mis-estimation in the Snowflake query compiler/optimizer. Estimating cardinality in query optimizers is a complicated topic. I’ve read papers on this and know people who have patents for some of the ways this is done in the IBM Db2 optimizer. Unfortunately, estimates are not generally available to identify if this is a potential cause. Using functions in filters or join predicates is one thing that may lead to a higher probability of a flipped join.
Example of Flipped Join Order
It is surprisingly difficult to simulate the full performance impact of this issue. I love to provide reproducible examples that prove my points. Here, I’ll focus on what a flipped join looks like instead of finding one that actually has a drastic performance impact.
Let’s look at an example of flipped join order. I’m going to run the same two queries on a smaller and a larger scale of the Snowflake sample data. Let’s start with a query that gets join order correct:
select *
from orders o
join lineitem oi on o.o_orderkey=oi.l_orderkey
;The query profile for that query on SF1 data looks like this:
Note that this query returned in 11s on an XS warehouse, with a result set of 6 million rows. We can see the lower row count is on the left. Indeed, the query profile makes the larger row count obvious by making the connecting arrows between the operators wider when there are more rows.
Now, let’s introduce a fairly simple equality predicate.
select *
from orders o
join lineitem oi on o.o_orderkey=oi.l_orderkey
where l_shipmode = 'AIR'
;This query returns in about 2.7s on my warm XS warehouse, and returns about 858,100 rows. The query profile looks like this:
Note that even with this simple predicate, we have a flipped join – the larger number is on the left. The two sides are relatively close to equal, so it is not a big deal for performance at this scale. We are able to apply the filter at the time the table scan is done, and by pushing a bloom filter from the build side to the join side, we are able to reduce the cardinality significantly, so I don’t actually hate this particular join flip – we were able to apply filters at table scan time to work with less data all the way along.
Let’s look at a function that we apply and get the exact same result set as the last query based on the data in this table:
select *
from orders o
join lineitem oi on o.o_orderkey=oi.l_orderkey
where substr(l_shipmode,1,1) = 'A'
;This query runs in about 2.7s on my warm XS warehouse, just like the last one, but the query profile looks quite different:
Here we see that the join is, indeed, flipped. we have a much larger number on the left than the right, and it’s not terribly close. It doesn’t make a performance difference at this scale. Let’s try going up to the largest scale factor on this data set – SF1000, and see what happens.
Here is the query and profile for our equality predicate:
select *
from orders o
join lineitem oi on o.o_orderkey=oi.l_orderkey
where l_shipmode = 'AIR'
;
Here, we can see that the join order is correct – the smaller table is on the left. This query runs in 33 minutes and 32 seconds on an XS warehouse. We did see spilling to local storage, but not to remote storage.
Now for the query with the more opaque predicate on the larger data set:
select *
from orders o
join lineitem oi on o.o_orderkey=oi.l_orderkey
where substr(l_shipmode,1,1) = 'A'
;This query runs in about 35 minutes on an XS warehouse:
At this scale, there’s still not a drastic difference in performance, but at 33 minutes vs. 35 minutes we are starting to see a bit more difference than at the smaller scale.
Taking Action
In general, what should you do if you encounter a flipped join? Clearly, if different, simpler predicates are an option then that could be a way to go. That’s often not an option. Simplifying a query may also help, but that can be an in-depth exercise in data modeling, and again may not always be possible. Materialization, using materialized views, dynamic tables, or manually materializing intermediate results may make simplification possible.
It is also possible to at least partially influence join order by using a CTE to perform joins first before combining with other conditions and joins, but this can be hit or miss. We can’t explicitly control the join order that Snowflake uses.
One of the major resolutions for this kind of problem is to contact Snowflake support. This is particularly the case for a flipped join where you don’t see a particular problem in complexity or opaque predicates. Sometimes Snowflake just gets the join order wrong. Depending on the actual cause of the flip behind the scenes, they have some things that they can do to help with the flipped join.
If a join flip does not appear to be an expensive part of the query or the query performance overall is not a problem, there is no need to take action to address a join flip. Remember that the join flip itself is not a problem. Only address a join flip if there is a problem involved.
