Join elimination using constraints
In the previous chapter, we set the RELY property on our constraints to pave the way for the performance gains we will now explore. Snowflake uses the RELY property to perform join elimination—avoiding unnecessary or redundant joins in queries. Depending on table size, joins can be memory-intensive, so avoiding them when possible can significantly improve performance and save compute credits.
Even if a join is specified in the query, but no columns from the joined table are selected as part of the result, the RELY property will tell the Snowflake query engine to avoid performing the join.
If we modify the previous query—joining CUSTOMER and LOCATION tables—but only request information from CUSTOMER, the RELY property will help us avoid the unnecessary join operation.
Figure 12.3 – A query with join elimination
A look at the query profile (which we will explore later in this chapter) confirms...
