TopolearnWhen a query is slow or slot-hungry, the execution details show exactly which stage to fix.
Every finished query has an execution graph (Execution details in the console, or job.query_plan in the API). It is a DAG of stages; each stage runs many parallel workers that read input, compute, and write output to the shuffle layer for the next stage. For each stage you see records read and written, and time split into wait, read, compute and write, with average and maximum across workers.
What to look for:
- Bytes and rows early: big reads that later filters throw away mean predicates aren't pruning. Check partition and cluster filters.
- Skew: a stage where max compute time far exceeds average means a few hot keys, such as NULL or "unknown" values, land on one worker. Filter them out or handle them separately.
- Joins: a small side is broadcast to every worker; two large sides are hash-joined after shuffling both by key. Reduce sides before joining: filter, aggregate, select fewer columns. A join that outputs more rows than its inputs often indicates a missing join key (a fan-out).
- Large ORDER BY without LIMIT forces a final sort on one worker; sort only what you return.
- "Resources exceeded" errors usually come from such single-worker steps or huge
ARRAY_AGGandSTRING_AGGgroups.
Proven optimizations: APPROX_COUNT_DISTINCT and the HLL_COUNT sketch functions instead of exact distinct counts at scale; materializing repeated CTEs into temporary tables, since a CTE referenced twice may be computed twice; avoiding JavaScript UDFs in hot paths; and window functions or QUALIFY instead of self-joins.
Example
-- Inspect stages of a finished job via INFORMATION_SCHEMA
SELECT
stage.name,
stage.records_read,
stage.records_written,
stage.compute_ms_avg,
stage.compute_ms_max, -- max >> avg => skew
stage.shuffle_output_bytes
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS j,
UNNEST(j.job_stages) AS stage
WHERE j.job_id = 'bquxjob_123abc'
ORDER BY stage.compute_ms_max DESC;
-- Exact vs approximate distinct users (approx: far less memory and shuffle)
SELECT COUNT(DISTINCT user_id) AS exact_users,
APPROX_COUNT_DISTINCT(user_id) AS approx_users
FROM web.events
WHERE DATE(event_ts) >= '2025-06-01';Remember
- Read stages: records in/out, wait/read/compute/write, avg vs max
- Max much greater than avg signals skew from hot keys
- Shrink join inputs; avoid unbounded ORDER BY; use approximate aggregates
Watch out for
- Referencing a heavy CTE several times and assuming it's computed once.