Join explosions in Snowflake can cause significant performance problems. I thought I’d spend some time digging into specific examples of cases where join explosions happen and how to work around the explosion in those examples.
Disjunct Join Predicates
What is a disjunct join predicate? It is generally a join predicate that contains an OR operator. Not all OR operators in join predicates lead to join explosions, but they might. When I’m scanning a query for red flags, OR in a join predicate is one of the things I look for.
Snowflake uses hash joins. Consider the way that hash joins work. The build side of a hash join builds a table of hashed values. The probe side then probes for matching values. This requires an equality predicate. In the case of a simple disjunct join predicate, we must generally explode the join to a cartesian product or partial cartesian product, and then use a filter after the join to filter our the rows that don’t meet our predicate’s requirements. Cartesian products are not as big of a deal at small scale, but at larger scales, can absolutely tank performance and explode cost.
In some cases an OR condition can sneak into a join predicate through the use of a COALESCE or a CASE operation, either in the query itself or in a CTE (Common Table Expression), subquery, or a view.
CTEs, subqueries, and views allow a huge amount of flexibility and may allow reuse of logic, but they can also hide complexity. Since the syntax for CTES, subqueries, and views is often all mixed together in the ways allowed by relational algebra at execution time by the optimizer, they can easily end up including disjunct predicates.
At a smaller scale, this type of problem might not even be noticeable. If I’m working with a few micropartitions, I’m unlikely to notice a performance difference, even on an XS warehouse. This is the kind of problem that tends to come out as data volumes scale. Often there is a threshould passed, and query execution goes from acceptable to abysmal. This is actually one of the main risks of ignoring join explosions at a smaller scale. They contribute to fragile performance that can become problematic at any time.
Example
Let’s take a simple example to see the impact of a disjunct join predicate along with one way to address it. The SNOWFLAKE_SAMPLE_DATA database doesn’t include a data model that lends itself well to this issue, so I’ll create a table that matches the type of structures we see all the time in the real world.
Everything here should easily run on a free trial account. None of these statements ran for more than 30 seconds on an XS warehouse. Note that while I was trying to get a data set to experiment on for this, for the first time I managed to run through all $400 worth of credits in a free trial account (first time I’ve manage to do this!), so be careful as you’re experimenting with this on different data sets.
Here’s how to set this up:
CREATE DATABASE IF NOT EXISTS EXPLOSION_EXP;
CREATE OR REPLACE SCHEMA EXP_SCH;
USE SCHEMA EXPLOSION_EXP.EXP_SCH;
SET min_date=TO_DATE('2024-02-07');
CREATE OR REPLACE TABLE EVENT_TAB AS
SELECT c_custkey as parent_custkey
, c_custkey + 256 as child_custkey
, to_date(dateadd('minute', row_number() over (order by random()) - 1500, $min_date)) as event_day
FROM snowflake_sample_data.tpch_sf1000.customer
ORDER BY RANDOM()
LIMIT 150000;Here, we’re just creating an event table that references parent and child customer ids. This is a common structure for tables logically, and a very simple representation of it. We’re only creating an event table with 150,000 rows – which is far smaller than we might see in the real world for an event table on a data set of this size.
Next, let’s come up with a sample query with a disjunct predicate that we might use with this data set.
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
SELECT c_name
, c_custkey
, e.parent_custkey
, e.child_custkey
, e.event_day
FROM snowflake_sample_data.tpch_sf1000.customer c
JOIN event_tab e
ON c.c_custkey = e.parent_custkey OR c.c_custkey = e.child_custkey
;Note that I start by disabling use of the result set cache. Since I’m playing with individual query run times, I don’t want to see use of the result set cache in these timings. If I were doing workload testing, I’d want to leave it enabled.
This is a fairly simple query with the disjuct join predicate: c.c_custkey = e.parent_custkey OR c.c_custkey = e.child_custkey
When I run this in my free trial account on an XS warehouse, I get an execution time of just under 10 seconds. The query profile looks like this:
The query profile makes it very clear that we are using a cartesian product here, and yet the cardinality coming out of the join is less than that of a full cartesian. This means that we are seeing an intermediate join explosion, where there is also a filter that reduces the cardinality after (but as a part of) that join. Clicking on the operator node for the join, we can see in the right-hand information panes that there is an “Additional join condition” invoved:
We can also see from the query profile that there is no filter applied prior to the join – the entire 150 million rows from the CUSTOMER table are going into that join.
Potential Solution
One potential solution for this type of join explosion is to essentially query each join and then UNION them all together. Let’s try that and see if it is faster, and then dig into what the query profile is telling us.
Here’s one way to write that query:
SELECT c_name
, c_custkey
, e.parent_custkey
, e.child_custkey
, e.event_day
FROM snowflake_sample_data.tpch_sf1000.customer c
JOIN event_tab e
ON c.c_custkey = e.parent_custkey
UNION
SELECT c_name
, c_custkey
, e.parent_custkey
, e.child_custkey
, e.event_day
FROM snowflake_sample_data.tpch_sf1000.customer c
JOIN event_tab e
ON c.c_custkey = e.child_custkey
;Note that here, I’m doing a separate query for each disjunct predicate and then using UNION to pull the data sets together. This modified query runs in just 3 seconds – about 1/3 of the excution time for the version that uses the disjunct join predicate. That is a huge difference in query time. The query profile looks like this:
There is a disadvantage to this plan. We are scanning the table twice, because our predicates don’t allow for partition pruning. However, this disadvantage is outweighed by being able to filter out many rows before we even get to the joins. The joins here only get 2.8 million rows going into them, as compared to the 150 million going into the last query’s join. This leads to the join being far less expensive than before, even if we have to essentially do it twice. Both joins show us an “Equality join condition” instead of an “Additional join condition”, indicating we’re applying this condition during the join instead of after the join as we were before.
UNION specifies that I want duplicates to be eliminated. Any time I use UNION, I always double check to see if I could reliably use UNION ALL. The UNION ALL version of this query looks like this:
SELECT c_name
, c_custkey
, e.parent_custkey
, e.child_custkey
, e.event_day
FROM snowflake_sample_data.tpch_sf1000.customer c
JOIN event_tab e
ON c.c_custkey = e.parent_custkey
UNION ALL
SELECT c_name
, c_custkey
, e.parent_custkey
, e.child_custkey
, e.event_day
FROM snowflake_sample_data.tpch_sf1000.customer c
JOIN event_tab e
ON c.c_custkey = e.child_custkey
;This query ran in a few hundred milliseconds less than the UNION syntax. In the case of this particular query, I did’t get any duplicates, so query insights actually suggested this modification. To know whether to use UNION ALL over UNION, I really have to understand both my data to know if duplicates are possible, and also the further use of this data to understand if deduplication is really needed.
Real World
This example is much simpler than syntax that we run into in the real world. I’ve seen this same issue tripping up queries that join dozens of tables, and figuring out the place to fix it may not be quite so obvious. It is worth experimenting with, as with larger data volumes, I’ve seen times where this issue caused a difference in query times measured in hours, on much larger warehouses. It can sometimes be worth introducing views or CTES to deal with problematic joins like this more explicitly.
Hopefully this provides an executable example that is simple enough to demonstrate the problem. Experimenting on your own data model is crucial to test the impact and the effort involved in mitigating this problem. Hopefully this small example shows the potential for performance improvement.
