I’ve recently finished a series on the Snowflake query profile. As a command-line gal at heart, I also want to cover two tools that are more text-based – explain and GET_QUERY_OPERATOR_STATS. I’ll focus on explain in this article, and then in a future article talk about GET_QUERY_OPTIMIZER_STATS and an interesting detail you can learn by running both and comparing the output.
Explain
Explain gets output about the optimizer’s plan for executing a query by by adding a single keyword before a query. The output is text-based rather than visual. Explain gives us the optimizer’s plan for executing the SQL provided without actually executing the full query. Because of this, explain does not even require a running warehouse. The output is fairly easy to work with for smaller queries, but gets more difficult to follow with more complexity and as more objects are a part of the plan.
Explain Example
Let’s start with a trivial example and then move on to a slightly more complicated example of looking at a query using explain.
Simpler Example
Here’s the simple query:
SELECT I_BRAND
, SUM(CS_NET_PROFIT)
FROM catalog_sales AS cs
JOIN item AS i
ON cs.cs_item_sk = i.i_item_sk
WHERE cs_coupon_amt > 28000
GROUP BY i.I_BRAND
;This is a simple inner join of two tables on a single-column join key with a filter that filters out most rows in the table, with a group by and an aggregate function. The table is not clustered on the column of the filter or on any column that aligns with it, but we are lucky to only read a few more micro-partitions than the number of rows returned. The query profile for this looks like:
Here we see scans of two tables. One achieves significant static partition pruning. We see most of the expense of the table is scanning 132 micro-partitions from the larger table of 54,922 micro-partitions. We can be happy that we were able to scan only 132 micro-partitions, or sad that we had to scan an entire micro-partition for each matching row from this table. If the performance of this query were critical to improve, we might consider clustering the table on cs_coupon_amt or adding a materialized view clustered on it.
Generating the explain output only requires prefixing our statement with EXPLAIN.
EXPLAIN
SELECT I_BRAND
, SUM(CS_NET_PROFIT)
FROM catalog_sales AS cs
JOIN item AS i
ON cs.cs_item_sk = i.i_item_sk
WHERE cs_coupon_amt > 28000
GROUP BY i.I_BRAND
;This gives us tabular output that looks like this:
Note the lack of any row counts in this output. The query profile shows us data for a query that has been executed, while explain shows us output for a query that has only been compiled/optimized, but not executed. This means that we only get micro-partition data and other information that is known at compile time. We do not get row counts or other information that is known only at runtime.
In this output, each row represents an operator node or operation. The columns here are:
- step – many queries are only a single step. If they are more than one step, the step number is denoted here. In our example, the step is always 1.
- id – unique identifier for each operation within the step in the query plan.
- parentOperators – an array of identifiers for the operation’s parent nodes. In the query profile, a parent is shown above its child with a link connecting the two. In our simple example, each step has only one direct parent. The only operation with more than one child is the join (#3), and it has two children corresponding to the two branches in our graphical representation in the query profile.
- operation – name of the operation, such as: Result, Filter, TableScan, Join, etc. GlobalStats is the final operator representing the entire query.
- objects – name of the object referenced by a table scan operation.
- alias – alias of a referenced object, if the object has been given an alias in the query.
- expressions – list of expressions relevant to the current operation such as filters, join predicates, projections, aggregations, etc. Note that this column is populated for every row except the final GlobalStats row, but that in each case it means something a little different. For the table scans, it holds the column list being scanned. For joins, it holds the join key. For aggregates, it holds the grouping key and aggregate expression.
- totalPartitions – total number of micro-partitions in the referenced database object.
- assignedPartitions – number of partitions from the referenced object that are left after static partition pruning.
- assignedBytes the number of bytes contained in the assignedPartitions.
Notice that throughout, there are unpopulated columns when they do not apply to that operator node.
The output for a simple query seems fairly straightforward. We can see there are things missing from what we might find in the query profile, and that is largely because explain is compile-time and not runtime data. To get runtime data, we’d need to use GET_QUERY_OPERATOR_STATS, which is beyond the scope of this article.
Explain output can also be generated using a JSON format if you’re more used to that, or even a text-friendly format. These are generated using syntax starting with EXPLAIN USING JSON... or EXPLAIN USING TEXT .... The JSON format for this query looks like this:
{
"GlobalStats": {
"partitionsTotal": 54924,
"partitionsAssigned": 134,
"bytesAssigned": 2247046144
},
"Operations": [
[
{
"id": 0,
"operation": "Result",
"expressions": [
"I.I_BRAND",
"SUM(SUM(SUM(CS.CS_NET_PROFIT)))"
]
},
{
"id": 1,
"operation": "Aggregate",
"expressions": [
"aggExprs: [SUM(SUM(SUM(CS.CS_NET_PROFIT)))]",
"groupKeys: [I.I_BRAND]"
],
"parentOperators": [
0
]
},
{
"id": 2,
"operation": "Aggregate",
"expressions": [
"aggExprs: [SUM(SUM(CS.CS_NET_PROFIT))]",
"groupKeys: [I.I_BRAND]"
],
"parentOperators": [
1
]
},
{
"id": 3,
"operation": "InnerJoin",
"expressions": [
"joinKey: (I.I_ITEM_SK = CS.CS_ITEM_SK)"
],
"parentOperators": [
2
]
},
{
"id": 4,
"operation": "TableScan",
"objects": [
"SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.ITEM"
],
"expressions": [
"I_ITEM_SK",
"I_BRAND"
],
"alias": "I",
"partitionsAssigned": 2,
"partitionsTotal": 2,
"bytesAssigned": 23811584,
"parentOperators": [
3
]
},
{
"id": 5,
"operation": "Aggregate",
"expressions": [
"aggExprs: [SUM(CS.CS_NET_PROFIT)]",
"groupKeys: [CS.CS_ITEM_SK]"
],
"parentOperators": [
3
]
},
{
"id": 6,
"operation": "Filter",
"expressions": [
"CS.CS_COUPON_AMT > 28000"
],
"parentOperators": [
5
]
},
{
"id": 7,
"operation": "JoinFilter",
"expressions": [
"joinKey: (I.I_ITEM_SK = CS.CS_ITEM_SK)"
],
"parentOperators": [
6
]
},
{
"id": 8,
"operation": "TableScan",
"objects": [
"SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.CATALOG_SALES"
],
"expressions": [
"CS_ITEM_SK",
"CS_COUPON_AMT",
"CS_NET_PROFIT"
],
"alias": "CS",
"partitionsAssigned": 132,
"partitionsTotal": 54922,
"bytesAssigned": 2223234560,
"parentOperators": [
7
]
}
]
]
}More Complex Example
With a simpler example under our belts, let’s look at a more complex example – specifically the same query that my series on the query profile used. This query looks like this:
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;In the query profile this query looks something like this:
Just at a glance, here we can see that there are many more operator nodes in this plan. There are both more tables being accessed and more complex logic being applied within the query. It is nearly impossible to represent the explain output on one screen. Here it is in two:
There are several complexities here. First, I often go to the bottom of this to see what is done first. That usually starts with a scan of a table or other object. That doesn’t work here, due to the CTE and the lag/lead methodology used here. This makes it much harder to visualize for me without the query profile. You can see that operator node #7 here has parents of not just #6, but also of #26 and #30.
The length of the data in the expressions column also makes reading this data in a tabular format somewhat difficult.
Summary
Hopefully this article helped to identify some of the information that is programmatically available using explain, and some information that is not available in this format. It is particularly useful for quickly looking at what static partition pruning is expected to do for a query. I plan to cover GET_QUERY_OPERATOR_STATS similarly in a future article.
