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 the 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 the CUSTOMER and LOCATION tables—but only request information from CUSTOMER, the RELY property will help us avoid the unnecessary join operation.
Figure 13.3: A query with join elimination
Quick tip: Need to see a high-resolution version of this image? Open this book in...
