The query execution details (also called the execution graph) show the stages of a query and how long each took. You find bottlenecks by looking for the stage that takes most of the time, and then asking why.
What you see
Each stage shows its timing split into wait, read, compute and write, and the number of records read and written. Stages are things like input (scan), join, aggregate, sort and output. For each timing there are average and maximum values across the workers that ran that stage.
What to look for
- Skew: the maximum compute time is far above the average. Some workers got far more data than others, usually because of a hot join or group key. A stage where max is 20 times the average has one slow worker holding everything back.
- Large wait time: workers waited for slots or for input from previous stages. This suggests slot contention. It is a capacity problem, not a query problem.
- Huge row counts: a join stage that outputs many more rows than it reads in means the join multiplies rows (a many-to-many join or missing condition).
- Spill: if a stage shows bytes spilled to disk, it did not have enough memory for its share of the data.
- Repartition stages: BigQuery inserts them to redistribute data for a join or aggregation. A large shuffle of many bytes is expensive.
- Read stage with many bytes: pruning did not help, so look at filters, partitions and clustering.
Over time
INFORMATION_SCHEMA.JOBS_TIMELINE shows slot usage by period. It shows whether the query got the slots it wanted, and when contention happened.
Typical fixes
Filter earlier, aggregate before joining, remove duplicated join keys, handle hot keys (for example filter NULL keys or split them out), and use approximate functions where exactness is not needed. After a change, run it again and compare the stage numbers instead of just the total time.
Also check whether a cached result hid the true cost: the execution details of a cached query show no work.