Query profiling and the query plan in Snowflake - Commands & Configuration

Introduction

When you run a query in Snowflake, it can take time and resources. Query profiling helps you see how your query runs step-by-step. The query plan shows the path Snowflake takes to get your data. This helps you find slow parts and fix them.

When a query takes too long to finish and you want to find the slow part.

When you want to understand how Snowflake reads and processes your data.

When you want to improve your query to use less compute and cost less.

When you want to check if your query uses indexes or filters efficiently.

When you want to compare different versions of a query to see which is faster.

Commands

This command shows the query plan in a readable text format. It explains how Snowflake will run the query before actually running it.

Terminal

EXPLAIN USING TEXT SELECT * FROM sales WHERE region = 'North';

Expected Output

Plan

----

|-- Filter: region = 'North'

|-- Scan: sales

This command shows the most recent query run in your session with details like execution time and bytes scanned. It helps you profile the query performance.

Terminal

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION()) ORDER BY START_TIME DESC LIMIT 1;

Expected Output

QUERY_ID  | QUERY_TEXT                                | START_TIME           | TOTAL_ELAPSED_TIME | BYTES_SCANNED

123456789 | SELECT * FROM sales WHERE region = 'North' | 2024-06-01 12:00:00 | 1500               | 1048576

This command shows detailed profiling information for the query with ID 123456789. It breaks down time spent on each step of the query.

Terminal

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_PROFILE('123456789'));

Expected Output

STEP_ID | OPERATION            | DURATION_MS | ROWS_PROCESSED

1       | Scan sales           | 500         | 10000

2       | Filter region='North' | 1000        | 2000

Key Concept

If you remember nothing else from this pattern, remember: the query plan shows how Snowflake runs your query, and profiling shows where time is spent.

Common Mistakes

Running EXPLAIN without USING TEXT and expecting readable output.

The default EXPLAIN output is in JSON, which is harder to read for beginners.

Use EXPLAIN USING TEXT for a simple, readable query plan.

Not checking query history to find the correct QUERY_ID before profiling.

Without the right QUERY_ID, you cannot get accurate profiling details.

Always query INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION to get the latest QUERY_ID.

Ignoring the bytes scanned and elapsed time in query history.

These metrics help identify if the query is scanning too much data or taking too long.

Use these metrics to focus your optimization efforts.

Summary

Use EXPLAIN USING TEXT to see a simple query plan before running your query.

Check your recent queries in INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION to find query IDs and performance details.

Use INFORMATION_SCHEMA.QUERY_PROFILE with a query ID to see detailed step-by-step timing and row counts.