Diagnosing why a query is slow and fixing it

BigQuery query performance issues typically stem from scanning too much data, inefficient joins, or poor use of partitioning and clustering. Troubleshooting relies on the Query Execution Details/Execution Graph and INFORMATION_SCHEMA views to pinpoint bottlenecks like shuffling, slot contention, or stage skew.

1 · Learn the must-know

• 1Use the Execution Details tab (query plan) in the BigQuery console to identify slow stages, high wait/read/compute times, and data skew between workers.
• 2Avoid SELECT * and instead select only needed columns, since BigQuery is columnar and charges/scans based on columns accessed, not rows.
• 3Filter and JOIN on partitioned and clustered columns (e.g., a DATE partition column) to enable partition pruning and reduce bytes scanned.
• 4Large joins should have the biggest table first/on the left and use appropriate join keys to minimize shuffle; avoid CROSS JOINs and unnecessary self-joins.
• 5Repeated queries against the same data can benefit from BigQuery's automatic caching or materialized views to avoid redundant scans and computation.
• 6Check INFORMATION_SCHEMA.JOBS_BY_* views for slot utilization, shuffle bytes, and query stats to detect if performance issues are due to resource contention (e.g., insufficient slots) rather than query design.
• 7Data skew (a few keys with disproportionately large row counts) causes uneven work distribution across slots; consider salting keys or restructuring queries to mitigate this.

2 · Check your understanding