How to Use Execution Plans in BigQuery

What is an execution plan in BigQuery?

An execution plan is BigQuery’s blueprint for running your SQL. It lists each stage, the number of input rows, slot time consumed, and shuffle behavior. Reading it lets you spot scans, joins, and expensive shuffles before or after a query runs.

How do I view the plan before running a query?

Prefix your statement with EXPLAIN.BigQuery returns a JSON-formatted plan without executing the query, so you see estimates only.

How do I view the actual runtime plan?

Use EXPLAIN ANALYZE. BigQuery executes the query, then adds runtime statistics—actual rows read, slot-ms, and timing—for every stage.

What options can I set?

Use OPTIONS to tweak behavior. For example, EXPLAIN OPTIONS(max_result_rows=20, format="text") limits output rows and switches from JSON to a tabular view.

When should I use EXPLAIN vs EXPLAIN ANALYZE?

Use EXPLAIN early in development to check cost without spending slots.Switch to EXPLAIN ANALYZE when you need real numbers on a finalized query or to compare optimization attempts.

Best practices for interpreting plans

Start at the bottom stage—this is the first operation. Identify full-table scans on large tables and add partitions or filters. Check join stages for broadcast vs shuffle; prefer broadcast on small lookup tables. Watch for high slot-ms; reduce by pruning columns and partitions.

Common mistakes and fixes

Ignoring estimated row counts: Large estimates mean higher cost.Add WHERE order_date >= CURRENT_DATE()-30 to restrict.

Using ANALYZE on huge ad-hoc queries: It runs the entire job, incurring cost. Start with plain EXPLAIN.

Practical example

The query below checks customer lifetime value. We show both estimated and actual plans.

-- Estimate
EXPLAIN
SELECT c.id,
SUM(o.total_amount) AS lifetime_value
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id;.

-- Actual runtime stats
EXPLAIN ANALYZE
SELECT c.id,
SUM(o.total_amount) AS lifetime_value
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id;


FAQs

Does EXPLAIN consume slots?

No, EXPLAIN returns metadata only. Only ANALYZE runs the job.

Why is my plan missing partition pruning?

Check that the filter uses the partition column directly and is not wrapped in functions.

How do I export the plan?

Click “Save Results” in the BigQuery UI or call the REST API’s jobs.getQueryResults with the job ID.