This article is the second in a series about the Snowflake Query Profile.
- Part 1: Accessing the Query Profile
- Part 2: The Query Execution Plan (this article)
- Part 3: The Information Panes
- Part 4: Using the Query Profile to Analyze a Query
If you’re not familiar with how to get to the Snowflake query profile, I recommend first reading Part 1: Accessing the Query Profile
No matter how you access the Query Profile, it will look something like this:
Notice that there are two primary areas here. The query execution plan on the left, and the information panes on the right. In this article we’ll focus on the query execution plan on the left.
Query Execution Plan
On the left is a graphical representation of the query plan, with actual numbers for the rows throughout the plan. Since actual numbers are shown here, if you look at the query profile for a running query, you’ll see the numbers so far. Refreshing will lead to the numbers being updated, assuming the query is still processing.
If you have worked with other database management systems, you may be familiar with a graphical representation of query access like the query profile. It consists of two primary elements. The boxes are referred to as operator nodes. They represent actions in the query execution plan like a table scan or an aggregation. The Snowflake documentation provides a list of the common operator nodes you might expect to see.
The lines between the operator node boxes are referred to as data links. Generally speaking, larger row counts in a data link may be represented by thicker lines. This makes it easier to see where larger amounts of data are flowing within the query, and may make it easier to spot things like a join explosion.
For example, in the query profile we’re using to illustrate things, we can zoom in on the thick arrows here:
We see here that there are about 3.466 billion rows flowing into the Aggregate [13] operator node. Hovering over the data link arrow causes the exact count to pop up, which can be useful when investigating aggregates that are expensive but do not reduce the row count. Note that there only about 2.537 million rows flowing out of that aggregate. This is expected behavior for an aggregate – they often reduce the row count, spending time/compute/memory to do so.
One important thing to keep in mind is that while execution is generally from the bottom to the top of the query plan, it is rarely linear – you may even see what appear to be circular patterns. Snowflake is an MPP system, so we are doing as many things as possible in parallel, until there is some condition that makes us stop and complete a section of the query profile before moving on to the next section.
Query Steps
Another useful piece of information that might show up in the query execution plan are different steps that Snowflake takes when running a query. Sometimes there are bits of the query that require a separate step to solve or that run more efficiently if we use a separate step. There can be many steps that show up here, and each step has it’s own query execution plan. The simplest example of this comes when running a CTAS (Create Table AS) statement:
Each step appears at the upper left with its own tab that can be clicked on. Step 1 consists merely of the Create Table [0] operator node, while all of our other query logic is on the tab for step 2.
This can be confusing, and is something I still sometimes miss when pulling up a query profile. I sometimes find myself staring at the query profile wondering why something is running so long if it looks that simple, only to realize I need to click on a later step to see the complex part of a query.
There is a special meaning to a step that is in the thousands (such as 1001 or 1003) or two thousand and something (such as 2001 or 2003) – this means a step has failed and has been retried. If the query is ultimately successful, it succeeded on one of the two possible retires of a step. Retries can be expensive, and often increasing the warehouse size can avoid them.
Summary
Hopefully this article provided useful information about how to read the query execution plan in the Snowflake Query Profile. In the next article in this series, we’ll cover the information panes on the right-hand side and the wealth of information available there. In the fourth entry in this series we’ll look at some common patterns or anti-patterns and how to recognize them in the query profile.
