Query Profiling & EXPLAIN Plan
Snowflake · Performance Deep Dive
Theory
Query Profile, opened from a query's details in Snowsight, shows the executed plan as a graph of operators such as TableScan, Filter, Join and Aggregate, with the time and rows for each. Key statistics include partitions scanned versus partitions total (pruning), bytes spilled to local or remote storage (memory pressure), and percentage scanned from cache. The most expensive nodes are listed so you can see where time went.
EXPLAIN shows the plan Snowflake would use without running the query. It is compiled in Cloud Services and does not use a warehouse, so it is a cheap way to check join order and partition estimates. For example, a join producing far more rows than either input in the profile is a sign of a missing or wrong join condition.
Why we use it
Guessing at why a query is slow wastes time and credits. The profile shows the actual bottleneck: poor pruning, spilling, an exploding join or queuing, so you fix the right thing.
When to use it
Open Query Profile for any slow or costly query before resizing a warehouse. Use EXPLAIN to sanity-check a big new query before running it. Use QUERY_HISTORY views to find which queries to profile in the first place.
Reading the Query Profile
The profile breaks a query into operator nodes (scan, join, aggregate, sort) with the percentage of total time and bytes each one consumed — the biggest boxes are where optimization effort pays off.
Spotting Common Problems
A huge 'bytes scanned' on a table scan node with a small 'partitions scanned after pruning' usually means poor clustering; a disproportionately slow join often means a missing filter pushed too late in the plan.
EXPLAIN vs Running the Query
EXPLAIN <query> returns the planned steps without executing anything, useful for sanity-checking an expensive query's shape before committing warehouse credits to actually running it.
How it works
- Slow query found in query history
- Open its Query Profile
- Partitions scanned close to total? Yes: Improve filters or clustering for pruning. No: Check spilling.
- Bytes spilled to local or remote storage? Yes: Use a larger warehouse or reduce data processed. No: Check joins for row explosion.
- Fix the bottleneck and compare the profile again
Example
SELECT QUERY_ID, TOTAL_ELAPSED_TIME/1000 AS elapsed_seconds,
PARTITIONS_SCANNED, PARTITIONS_TOTAL, PERCENTAGE_SCANNED_FROM_CACHE,
BYTES_SPILLED_TO_LOCAL_STORAGE
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT => 20))
ORDER BY TOTAL_ELAPSED_TIME DESC;Key points
- Query Profile shows where time and bytes actually went, after execution
- EXPLAIN previews the plan without running the query
- Partitions scanned near total means poor pruning
- Remote spilling is a strong sign the warehouse is too small
- A join outputting far more rows than its inputs suggests a bad join condition