EXPLAIN tells you the plan the database picked. It is a tree, read bottom-up: leaves are scans, the root is the final result.
Read it bottom-up
Find the scans first. Each scan node tells you: which table, what filter pushed down, and the estimated row count. If the estimate is wildly off, your stats are stale.
Three red flags that matter
- Sequential scan over a huge table when an index existed (missing or unusable index).
- Estimated rows off by 100x from actual rows (bad stats → wrong join order).
- Hash join with build side larger than memory (spills to disk).
Snowflake and BigQuery quirks
Snowflake's plan profile shows pruning — partitions scanned vs total. BigQuery shows slot time and shuffle bytes. In both, the win is usually pruning more partitions, not rewriting joins.
Pair EXPLAIN with a lineage view: the plan tells you what the planner did, the lineage tells you which columns the planner had to keep alive end-to-end. Together they answer 'why is this slow?' faster than either alone.