? Use Query Plan Explanation

In BigQuery UI:

• Open query

• Click Execution Details

• View stages graph

Or:

? What to Look For

1️⃣ Skewed Stages

If one stage:

• Takes 80% runtime

• Has large shuffle

Likely:

• Data skew

• High cardinality group by

• Uneven join key distribution

2️⃣ Large Shuffle Output

If shuffle output >> input:

• You’re exploding rows

• Probably cross join or bad join condition

3️⃣ Slot Idle Time

If:

• Many slots allocated

• Low CPU utilization

Likely:

• IO bottleneck

• Waiting on remote shuffle

4️⃣ Spill to Disk

If stage shows:

• Spill to disk

Means:

• Not enough memory per slot

• Large aggregations

• Large join state

Solution:

• Pre-aggregate

• Reduce cardinality

• Break query into steps

? Identify Slot Contention vs Query Problem

If:

• Query waits long before starting
  → Slot contention

If:

• Query starts immediately but runs long
  → Query design issue

? Most Common Performance Killers

1. SELECT *

2. Missing partition filters

3. High-cardinality GROUP BY

4. Unfiltered JOINs

5. Repeated subqueries instead of temp tables

6. Data skew on join keys

7. Excessive DISTINCT

? Enterprise Architecture Pattern (100TB+)

For large warehouses:

• Partition all fact tables

• Cluster on major join keys

• Separate ETL & BI reservations

• Enable autoscaling

• Pre-aggregate for dashboards

• Monitor slot_ms daily

• Cap user concurrency

? Mental Model Summary

Think of BigQuery as:

Storage Layer → Scan Slots
Shuffle Layer → Network + Memory Heavy
Compute Layer → Slot Parallelism
Reservations → Compute Budget
Autoscaling → Burst Safety
Execution Plan → X-ray of Query