BigQuery Performance Optimisation: Speed Up Queries and Reduce Cost

BigQuery performance optimisation in simple terms

BigQuery is a columnar data warehouse that spreads work across thousands of machines. That architecture is extremely fast for large analytical queries, but it also means that waste scales quickly. A small inefficiency in your SQL can multiply across thousands of workers and terabytes of data.

BigQuery queries get slow for four main reasons:

• Reading too much data. Scanning columns or partitions your query does not need.
• Shuffling too much data. Joins and aggregations that force BigQuery to redistribute large volumes of data between workers.
• Sorting or aggregating too much. Operations like ORDER BY on billions of rows, or exact DISTINCT counts on high-cardinality columns.
• Recomputing expensive work. Running the same heavy transformations repeatedly instead of storing intermediate results.

In BigQuery’s on-demand pricing model, performance and cost are tightly linked. Queries that scan more bytes take longer and cost more. Most of the techniques on this page improve both speed and cost at the same time.

How BigQuery query performance works

BigQuery performance depends on five factors: how much data the query reads, how much data it shuffles between workers, how much compute it requires, how large the output is, and how much concurrent workload is competing for slots. The first three are where most tuning effort pays off.

Read less data

BigQuery uses columnar storage. It reads only the columns your query selects and only the partitions your WHERE clause targets. Choosing fewer columns and adding partition filters are the two fastest ways to reduce bytes scanned.

Shuffle less data

Joins, GROUP BY, and window functions require BigQuery to redistribute rows between workers. This “shuffle” stage is often the slowest part of a query. Filtering tables before joining them and keeping small lookup tables small enough for broadcast joins both reduce shuffle volume.

Do less repeated work

If multiple dashboards or scheduled queries run the same transformations on the same raw data, that work runs from scratch each time. Materialised views and persisted intermediate tables let you compute expensive transformations once and reuse the results.

Diagnose before guessing

Before tuning anything, check the query execution plan. After running a query in the BigQuery console, open the Execution details tab. It shows each stage, how many workers ran, how much data each stage read and wrote, and where time was spent. Look for stages with high read volume or long elapsed time as your starting point.

For finding your most expensive queries over time, query INFORMATION_SCHEMA.JOBS:

-- Find the most expensive queries in the last 24 hours
SELECT
  job_id,
  total_bytes_processed,
  total_slot_ms,
  creation_time,
  SUBSTR(query, 1, 200) AS query_preview
FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND job_type = 'QUERY'
ORDER BY total_bytes_processed DESC
LIMIT 10;

INFORMATION_SCHEMA.JOBS is region-specific. Replace region-us with the region that matches your dataset location (for example, region-europe-west2).

Highest-impact optimisations first

These techniques are listed roughly in order of impact. Start at the top and work down.

1. Partition pruning

A partitioned table divides data into segments, usually by date. When your WHERE clause filters on the partition column, BigQuery skips every partition that does not match. On a table with two years of daily data, filtering to a single day means BigQuery reads roughly one day’s worth of data instead of all of it.

When to use it: any query on a large table that has a date or timestamp column you can filter on. For ingestion-time partitioned tables, filter on _PARTITIONTIME instead of a regular column.

-- No pruning: scans the entire table
SELECT user_id, event_type
FROM analytics.events
WHERE user_id = 'usr-1234';

-- Partition pruning: scans only the 2026-03-01 partition
SELECT user_id, event_type
FROM analytics.events
WHERE DATE(event_timestamp) = '2026-03-01'
  AND user_id = 'usr-1234';

Why this matters: partition pruning is usually the single largest cost and performance improvement you can make on a large table. It turns a full table scan into a scan of only the data your query needs.

2. Clustering and selective filters

Clustering sorts the data within each partition by one or more columns. When your query filters on a clustering column, BigQuery can skip entire blocks of data within the partition. Clustering is not an index in the traditional sense. It improves block-level pruning, meaning BigQuery reads fewer storage blocks from disk, but it does not enable row-level lookups.

When to use it: when your queries frequently filter or aggregate on specific columns beyond the partition column. For example, partition by date and cluster by country if most queries filter by both.

When it will not help: if your queries do not filter on the clustering columns, or if the clustering column has very low cardinality (for example, a boolean), the block-pruning benefit is minimal.

-- Table created with clustering
CREATE TABLE analytics.events_clustered
PARTITION BY DATE(event_timestamp)
CLUSTER BY country, event_type
AS SELECT * FROM analytics.events;

-- This query benefits from both partition pruning and cluster pruning
SELECT user_id, event_type, revenue
FROM analytics.events_clustered
WHERE DATE(event_timestamp) = '2026-03-01'
  AND country = 'GB';

Why this matters: clustering adds a second layer of data skipping on top of partitioning. Together, they can reduce bytes scanned dramatically compared to an unpartitioned, unclustered table.

3. Stop using SELECT *

BigQuery’s columnar storage reads only the columns your query requests. If you select 5 columns from an 80-column table, only those 5 are read from disk. SELECT * reads all 80. On wide tables, this can mean scanning 10 to 20 times more data than necessary.

When to use column selection: always, in every production query, CTE, and subquery.

When it will not help: on narrow tables with only a few columns, the difference is small. But the habit should be universal.

-- Expensive: scans every column
SELECT *
FROM analytics.events
WHERE DATE(event_timestamp) = '2026-03-01';

-- Cheaper: scans only three columns
SELECT user_id, event_type, revenue
FROM analytics.events
WHERE DATE(event_timestamp) = '2026-03-01';

Why this matters: column selection is the easiest optimisation to apply. It requires no schema changes and often delivers a large reduction in bytes scanned.

4. Filter and aggregate before joins

Joins are expensive in BigQuery because they require shuffling data between workers, especially large-to-large joins. Joining two large tables before filtering means shuffling all rows, then discarding most of them. Push WHERE filters and aggregations into subqueries or CTEs so that each side of the join is as small as possible before the join happens.

When to use it: any query that joins two or more tables, especially when at least one table is large.

-- Expensive: joins full tables, then filters
SELECT o.order_id, c.name
FROM analytics.orders o
JOIN analytics.customers c ON o.customer_id = c.customer_id
WHERE o.order_date = '2026-03-01';

-- Better: filter before joining
SELECT o.order_id, c.name
FROM (
  SELECT order_id, customer_id
  FROM analytics.orders
  WHERE order_date = '2026-03-01'
) o
JOIN analytics.customers c ON o.customer_id = c.customer_id;

Why this matters: pre-filtering reduces the volume of data that needs to be shuffled during the join, which is often the most time-consuming part of a query.

5. Use materialised views or persisted intermediate tables

When multiple queries or dashboards run the same transformation on the same base data, each run re-scans and reprocesses from scratch. Materialised views let you define the transformation once; BigQuery incrementally refreshes the result when the base tables change. For more complex ELT pipelines, you can write intermediate results to permanent tables on a schedule.

When to use it: any aggregation or transformation that runs more than once on the same data. Common examples include daily summary tables, cleaned event data, and pre-joined dimension tables.

When it will not help: one-off exploratory queries that will not be repeated. Also, materialised views have restrictions (for example, they cannot include certain functions or multiple levels of aggregation).

-- Create a materialised view for daily revenue by country
CREATE MATERIALIZED VIEW analytics.daily_revenue_by_country
PARTITION BY report_date
CLUSTER BY country
AS
SELECT
  DATE(event_timestamp) AS report_date,
  country,
  SUM(revenue) AS total_revenue,
  COUNT(*) AS order_count
FROM analytics.events
GROUP BY report_date, country;

Why this matters: materialised views and persisted tables eliminate redundant computation. If five dashboards all query the same aggregated data, computing it once instead of five times cuts scan volume significantly.

6. Use approximate aggregations when exactness is not required

COUNT(DISTINCT col) requires BigQuery to collect and deduplicate every unique value, which is memory-intensive on high-cardinality columns. APPROX_COUNT_DISTINCT uses HyperLogLog++ to produce an estimate that is typically within about 1% of the exact count, using far fewer resources.

When to use it: dashboards, trend analysis, and reporting where a small margin of error is acceptable. Most analytics use cases fall into this category.

When it will not help: when exact counts are a contractual, regulatory, or billing requirement.

-- Expensive: exact distinct count
SELECT COUNT(DISTINCT user_id) AS exact_users
FROM analytics.events
WHERE DATE(event_timestamp) = CURRENT_DATE();

-- Faster: approximate distinct count
SELECT APPROX_COUNT_DISTINCT(user_id) AS approx_users
FROM analytics.events
WHERE DATE(event_timestamp) = CURRENT_DATE();

Why this matters: on tables with millions or billions of rows, the resource savings from approximate aggregation can be the difference between a query completing and a query hitting resource limits.

7. Avoid sharded tables and sloppy wildcard patterns

Date-sharded tables (for example, events_20260301, events_20260302) are an older BigQuery pattern. Querying them requires wildcard table syntax (events_*), which comes with overhead: BigQuery must resolve the table list, read metadata for each shard, and combine the results. Partitioned tables handle this more efficiently because BigQuery manages the partitions natively.

When to use it: if you have existing sharded tables, migrate them to partitioned tables when practical. For new tables, always use partitioning instead of date sharding.

When it will not help: if you have a small number of shards that are rarely queried, the overhead is negligible and migration may not be worth the effort.

-- Wildcard query on sharded tables (less efficient)
SELECT user_id, event_type
FROM `analytics.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260307';

-- Same query on a partitioned table (more efficient)
SELECT user_id, event_type
FROM analytics.events
WHERE DATE(event_timestamp) BETWEEN '2026-03-01' AND '2026-03-07';

Why this matters: partitioned tables have lower metadata overhead, simpler SQL, and better integration with features like clustering and materialised views.

8. Be careful with ORDER BY, DISTINCT, and repeated CTEs

ORDER BY on a large result set requires BigQuery to sort all rows, which is compute-intensive and can spill to disk. DISTINCT on many columns is effectively a GROUP BY on all of them. Repeated CTEs (Common Table Expressions) are evaluated each time they are referenced in a query, not cached.

When to use it: if you need sorted output, add a LIMIT to cap the sort. If you reference a CTE more than once in a query, consider writing it to a temporary table instead.

When it will not help: a single reference to a CTE is fine. ORDER BY with a small LIMIT is fine. These become problems at scale or with repetition.

-- Expensive: sorts billions of rows
SELECT user_id, event_type, event_timestamp
FROM analytics.events
ORDER BY event_timestamp DESC;

-- Better: filter first, then sort only what you need
SELECT user_id, event_type, event_timestamp
FROM analytics.events
WHERE DATE(event_timestamp) = CURRENT_DATE()
ORDER BY event_timestamp DESC
LIMIT 100;

Why this matters: unnecessary sorts and repeated CTE evaluations add compute cost that is easy to avoid once you are aware of it.

Partitioning vs clustering vs materialised views

These techniques are complementary. A common production pattern is a partitioned and clustered base table with materialised views for frequently run aggregations.

When query tuning is enough vs when you need a table redesign

Sometimes the SQL is the problem. Sometimes the table structure is the problem. Here is how to tell the difference.

SQL changes are enough when: your table is already partitioned and clustered, but queries use SELECT *, skip partition filters, or join before filtering. These are query-level fixes that do not require changing the underlying tables.

A table redesign is needed when:

• The table has no partitioning and you query it by date range regularly.
• Queries repeatedly join the same large tables and the join is always the bottleneck. Denormalising the data (flattening related tables into one, or using STRUCT and ARRAY types to nest related records) removes the join entirely.
• The same expensive transformation runs in multiple queries or on a schedule. A precomputed table or materialised view eliminates the repeated work.
• The table uses date sharding instead of native partitioning.

If you are building a data pipeline from scratch, design the target tables with partitioning, clustering, and appropriate denormalisation from the start. Retrofitting these onto existing tables is possible but requires creating new tables and copying data.

When to use this guidance

Analysts writing ad hoc SQL. Start with the basics: select only the columns you need, always filter on the partition column, and check the execution plan when a query takes longer than expected. If you are new to BigQuery, the running your first query guide covers the fundamentals.

Data engineers building ELT pipelines. Focus on table design: partitioning strategy, clustering columns, and materialised views for commonly aggregated data. Pre-filter and pre-aggregate within pipeline stages to avoid passing unnecessary data downstream.

BI and dashboard teams. Dashboards run the same queries repeatedly. Materialised views and precomputed summary tables are your primary tools. A dashboard query that scans raw event data on every refresh is a recurring cost that a materialised view eliminates.

Teams troubleshooting expensive scheduled queries. Use INFORMATION_SCHEMA.JOBS to find your most expensive queries by bytes scanned and slot time. Then work through the optimisation list on this page, starting with partition pruning and column selection.

Not every performance problem is a tuning problem. If your workload involves frequent point lookups or transactional writes rather than analytics, BigQuery vs Cloud SQL explains when a relational database is the better fit.

BigQuery performance optimisation vs BigQuery cost optimisation

Performance optimisation and cost optimisation overlap heavily in BigQuery because on-demand pricing charges per byte scanned. Most techniques that reduce bytes scanned make queries both faster and cheaper simultaneously.

Where they overlap: partition filters, column selection, clustering, materialised views, and pre-filtering before joins all reduce both execution time and cost.

Where they differ:

• Slot contention and concurrency. A query may scan very little data but still run slowly because it is competing with other queries for slot resources. This is a performance problem, not a cost problem under on-demand pricing.
• Compute-heavy operations. Large sorts, complex regular expressions, and JavaScript UDFs add compute time without necessarily increasing bytes scanned.
• Capacity pricing. Under capacity pricing, you pay for reserved slots regardless of bytes scanned. Performance tuning still matters because faster queries free slots for other work, but the cost model is different.
• Storage costs. Materialised views and precomputed tables reduce query cost but add storage cost. The trade-off almost always favours materialisation, but it is worth tracking.

If your goal is specifically to reduce your BigQuery bill, the dedicated BigQuery cost optimisation guide covers pricing models, budget alerts, and maximumBytesBilled safeguards alongside the scan-reduction techniques.