In a recent series of articles, I walked through the query optimizer. I’ve also covered using EXPLAIN to look at queries. One of the primary disadvantages of EXPLAIN is that it does not execute the query, and is therefore missing the full picture that the query profile supplies. The answer to that is using GET_QUERY_OPERATOR_STATS, which gives us programmatic access to the data available in the query profile. One disadvantage is that it is a bit harder to show visually in this blog format.
GET_QUERY_OPERATOR_STATS
GET_QUERY_OPERATOR_STATS is a system table function. It takes a query id as input and produces a table of output that represents data that is available in the query profile. The output of GET_QUERY_OPERATOR_STATS is at least vaguely tabular in format, but includes several objects that contain different arrays or objects depending on what the operator node is doing.
Query ID
The query id is a required input for GET_QUERY_OPERATOR_STATS. See What is a Query ID in Snowflake? to learn more about query_ids and where to find them.
Output
The table of data returned for a query includes the following columns:
- QUERY_ID – Identifier of the query, which is an internal, system-generated identifier for the SQL statement. See What is a Query ID in Snowflake? to learn more about query_ids.
- STEP_ID – Identifier of the step in the query plan. Many queries have only one step.
- OPERATOR_ID – The operator’s identifier. This is unique within the query. Values start at 0. The query is executed roughly in reverse order of the operator id numbers, though not exactly, and there are often multiple things running in parallel.
- PARENT_OPERATORS – Identifies the operator above this one in the graph. Represented as an array to allow for multiple parents. The NULL value is a good way to identify the final operator in the plan – usually the Result operator.
- OPERATOR_TYPE – A description of what the operation is, e.g. TableScan or Filter. A list of most operator types is available in the attributes section of the Snowflake documentation.
- OPERATOR_STATISTICS – a variable set of details contained in an OBJECT. Includes information about the operator such as the number of rows. A list of statistics is available in the Snowflake documentation.
- EXECUTION_TIME_BREAKDOWN – A break down of execution time for the operator, again stored as an OBJECT.
- OPERATOR_ATTRIBUTES – Information about the operator. This information depends upon the operator type. Attributes are documented in the Snowflake documentation.
An Example of Using GET_QUERY_OPERATOR_STATS
A query plan isn’t very linear, so representing it as a fairly flat table leads to objects and arrays to represent some of the structures here. This means that the output from the query we’ve been using as an example in this series looks something like this:
In a visual representation like this, we end up truncating and being unable to see a lot of the data. But it’s also difficult to really share in a textual format, so let’s look at a single row(operator):
QUERY_ID 01be0030-0306-187c-0000-000e4a6b9039
STEP_ID 1
OPERATOR_ID 23
PARENT_OPERATORS [
22
]
OPERATOR_TYPE TableScan
OPERATOR_STATISTICS {
"io": {
"bytes_scanned": 18572374032,
"percentage_scanned_from_cache": 0.8691,
"scan_progress": 0.2416
},
"output_rows": 3466142468,
"pruning": {
"partitions_scanned": 13271,
"partitions_total": 54922
}
}
EXECUTION_TIME_BREAKDOWN{
"local_disk_io": 1.563691838291381e-01,
"overall_percentage": 4.302059496567506e-01,
"processing": 6.178489702517162e-02,
"remote_disk_io": 1.945080091533181e-01,
"synchronization": 1.754385964912281e-02
}
OPERATOR_ATTRIBUTES {
"columns": [
"CS_SOLD_DATE_SK",
"CS_CALL_CENTER_SK",
"CS_ITEM_SK",
"CS_SALES_PRICE"
],
"table_name": "SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.CATALOG_SALES"
}
Applications
At a glance, this is harder for a human to read than the query profile. However, if you want to programmatically process query profile data, this is the way to do it. There are a couple of advantages to this output I’d like to point out.
Static vs. Dynamic Partition Pruning
As mentioned in Static and Dynamic Partition Pruning in Snowflake, there are two types of pruning that can occur. Static pruning happens during query compilation, so the number of micro-partitions from EXPLAIN output is what we get from static pruning alone. Dynamic pruning happens at execution time, and is reflected in the query profile or in the GET_QUERY_OPERATOR_STATS output. This means that by comparing the output from EXPLAIN and the output from GET_QUERY_OPERATOR_STATS on pruning, we can understand whether the pruning we are seeing for a query is static or dynamic.
For the query we’ve been looking at in this series, EXPLAIN shows us this row for the table scan of SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.CATALOG_SALES:
The number of total partitions is 54,922, and the number of partitions scanned (assigned) is also 54,922, meaning that static pruning does not allow us to eliminate any micro-partitons.
The OPERATOR_STATISTICS column for the table scan from GET_QUERY_OPERATOR_STATS looks like this:
{
"io": {
"bytes_scanned": 18572374032,
"percentage_scanned_from_cache": 0.8691,
"scan_progress": 0.2416
},
"output_rows": 3466142468,
"pruning": {
"partitions_scanned": 13271,
"partitions_total": 54922
}
}
This tells us not just that we were able to scan only 13,271 micro-partitions, but that it is dynamic and not static pruning that led to the pruning in this case.
Archiving This Information
It can be useful to save the information available here for queries you’re working on optimizing or for other queries that come along to be able to compare over time. This medium article describes one method for doing so based on specific query tags.
Storing the data this way allows you to retain it longer than it is available in a query profile, and in a format that may be easier to compare programmatically.
Summary
GET_QUERY_OPERATOR_STATS is another way to access the information available through the query profile. It is just one tool that should be part of a tool box for digging into queries in Snowflake.
