This article is the fourth in a series about the Snowflake Query Profile.
- Part 1: Accessing the Query Profile
- Part 2: The Query Execution Plan
- Part 3: The Information Panes
- Part 4: Using the Query Profile to Analyze a Query(this article)
If you’re not familiar with how to get to the Snowflake query profile, I recommend first reading Part 1: Accessing the Query Profile
So far in this series, I’ve focused on how to get to the query profile and how to read the query profile. This article focuses on why, and some patterns and problems we can identify in the query profile. If you want to follow along, I’ve included the query I’m using for a starting example for these articles at the bottom of this article. This article also includes other queries to demonstrate different patterns.
General Performance Information
Like the explain facilities on other platforms, the Query Profile’s first goal is to help you understand where the expense in a query is. As with all information Snowflake provides, the goal is to provide actionable information without the less important details. Understanding where in a query the expense lies may give an idea of how to play with the query to make it less expensive.
One piece of information you’ll find missing here and in the actual explain output is any kind of estimation for time. Some platforms provide timing estimates in real time units. When I worked with Db2, the estimates were in “timerons” which was a unit that was only useful for comparing with other queries, and could not be converted to actual time units.
Partition Pruning
One of the easiest ways to get better performance is simply to skip reading data that we don’t need. Snowflake uses guaranteed-correct metadata to identify micro-partitions that we don’t even have to look at in any way. For more information on the basics of micro-partition pruning, check out Snowflake Partition Pruning: What is it, and why does it matter?. For a more in-depth look at partition pruning, check out Static and Dynamic Partition Pruning in Snowflake. We can look at partition pruning at the level of the entire query. The Statistics pane when no operator is selected provides this information on the level of the entire query:
From this, we can see that the tables involved in this query consisted of 54,926 micro-partitions. This query scanned 13,275 of those micro-partitions. We saw some partition pruning, but still read a significant portion of the tables to satisfy this query.
This data is also available for each table scan. By clicking on TableScan [23] in the plan in the left pane, the statistics pane changes to display only the information for this one table:
Here we can see there were 54,922 micro-partitions in the table at the time of this query, and we were able to prune down to only 13,271 micro-partitions.
Seeing partition pruning is great, but just checking a box that partition pruning is occurring is not enough. Let’s say I’m running a query that returns only one row, and it’s against a table with 50,000 micro-partitions. If I look at the query profile and find I’m reading only 5,000 micro-partitions, I might think “Great, I scanned only 10% of the table!”. Depending on the logic and shape of the query and the data, I might be able to reduce that down to only a single micro-partition through things like clustering, search optimization service, adding predicates, or other techniques.
If a table scan is one of the hogs of CPU time within a query profile, that is a good clue that partition pruning may not be ideal. Though partition pruning can absolutely be the most expensive part of a well-tuned query, depending on how much data must be scanned to fulfill the query.
Remote Spilling
Remote spilling happens when we’re doing something with data while satisfying a query (sorting, joining, and other activities), and we need to store an intermediate result set or other partial data. We first try to keep that in memory. If it is not possible to keep it in memory, we will spill, locally (to SSD on the virtual warehouse). If the intermediate data set we need is too large even for that, then we’ll write data out to cloud object storage. We then have to read that data back in from cloud object storage. This is called remote spilling. Remote spilling can be orders of magnitude slower than local spilling or avoiding spilling all together.
We don’t see remote spilling in the query we’ve been using for this article series. Remote spilling may happen for a query at smaller warehouse sizes and not at larger warehouse sizes. Here’s the simple query I ran to encounter remote spilling on an XS virtual warehouse:
select *
from catalog_sales
order by cs_sales_price desc;This is simply a query that will scan and sort a lot of data, and at the XS virtual warehouse size, we get remote spilling:
We can see here that we spilled over 5 TB to remote storage.
Taking the same query and scaling up the warehouse size to XL eliminates the remote spilling, and the same query runs in 36 minutes and 13 seconds:
We can see that there was no remote spilling.
On an XS virtual warehouse, this query took 15 hours, 12 minutes, and six seconds to run. On an XL warehouse, where remote spilling did not occur, it took 36 minutes and 13 seconds. If we look at the cost of these two queries alone, using the XS cost about 15.2 credits (15.2 hours times one credit per hour), and the XL cost 9.7 credits(~.6 hours times 16 credits per hour). Because we were able to eliminate remote spilling, the cost was significantly less, for faster performance.
Note that remote I/O is not remote spilling. Most queries will require some remote I/O, as that is simply how Snowflake scans data.
Cartesian Products
Some query profile plans make a true cartesian product very clear by calling it a cartesian join:
In other cases, a partial cartesian product may be less obvious.
Join Explosions
To understand join explosions, check out Problematic Query Patterns in Snowflake: Join Explosion. Some join explosions are very obvious in the query profile, while others can be more difficult to identify, and both are covered in that article.
Flipped Joins
Because Snowflake uses hash joins, it is generally best to have the lower cardinality on the left side (or build side) of a join. A good mnemonic to remember this is “Lower Left”.
In some situations where the higher cardinality is on the left or build side, this can be a significant performance issue. Take a look at Flipped Joins in Snowflake for more details. In general, flipped joins may require help from support to avoid – there is not much users can do to influence join order. But they are visible in the query profile.
Summary
These are not the only useful patterns that you might identify. I’d love to hear in the comments below any other patterns that you look for or find. Hopefully this article, in context with the whole series, provides a good starting point for looking at query performance using the query profile in Snowflake.
References
For reference, we are using the following query for most of the examples of the query profile that we’ve explored in this article. You can run this query as-is in your own free demo account to explore the output. This article does share a couple of screen shots from different queries to illustrate specific points. Snowflake changes all the time, so your query plan, timing, and query profile may differ from what I’ve shared here.
with
v1 as(
select i_category, i_brand,
cc_name,
d_year, d_moy,
sum(cs_sales_price) sum_sales,
avg(sum(cs_sales_price)) over
(partition by i_category, i_brand, cc_name, d_year) avg_monthly_sales,
rank() over (partition by i_category, i_brand, cc_name order by d_year, d_moy) rn
from item, catalog_sales, date_dim, call_center
where cs_item_sk = i_item_sk and
cs_sold_date_sk = d_date_sk and
cc_call_center_sk= cs_call_center_sk and
(
d_year = 2001 or
( d_year = 2001-1 and d_moy =12) or
( d_year = 2001+1 and d_moy =1)
)
group by i_category, i_brand,
cc_name , d_year, d_moy),
v2 as(
select v1.i_brand
,v1.d_year, v1.d_moy
,v1.avg_monthly_sales
,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
from v1, v1 v1_lag, v1 v1_lead
where v1.i_category = v1_lag.i_category and
v1.i_category = v1_lead.i_category and
v1.i_brand = v1_lag.i_brand and
v1.i_brand = v1_lead.i_brand and
v1.cc_name = v1_lag. cc_name and
v1.cc_name = v1_lead. cc_name and
v1.rn = v1_lag.rn + 1 and
v1.rn = v1_lead.rn - 1)
select *
from v2
where d_year = 2001 and
avg_monthly_sales > 0 and
case when avg_monthly_sales > 0
then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
else null
end > 0.1
order by sum_sales - avg_monthly_sales, nsum
limit 100;