BigQuery query performance best practices

Which BigQuery performance advice is worth following, measured. Slot-time and run time for LIKE vs regex, approximate counts, JavaScript UDFs, ORDER BY, window functions and NTILE, plus advice from Google's guide that did not hold up in our tests.

Most BigQuery advice is about bytes, because bytes are the bill: read fewer columns, prune partitions, don’t SELECT *. That advice is in how queries are billed and partitioned tables. This page is about the other half, compute. Two queries can read the same bytes and differ by 40 times in slot-time. Under on-demand pricing that difference is not on your bill, but it is how long you wait, and under a reservation it is the capacity you pay for.

Google’s guide lists dozens of practices. We ran the ones that can be tested on our own tables of 2 to 10 million rows, read at the job’s slot-time and elapsed time, and kept the ones that moved. We repeated each query 3 to 8 times and report medians, because slot-time for the same query can vary by 30 percent or more from run to run. See how a query runs for what slot-time is and how to read it.

What moved the numbers

The factor is the slower number divided by the faster. The rows with a second time give the elapsed time, which can move more than slot-time. A final ORDER BY has almost no parallelism, so the clock suffers more than the slot-seconds do.

Limit before you rank

A DENSE_RANK() over a whole table sorts the whole table, and a LIMIT 10 after it doesn’t help: the plan for ours had 13 stages and moved 1.8 GB between them. Take the first rows in a subquery, then rank them. The two forms give the same answer when the window orders by what the subquery orders by:

WITH accounts AS (
  SELECT 7 AS id, DATE '2011-03-02' AS created UNION ALL
  SELECT 3, DATE '2008-07-31' UNION ALL
  SELECT 9, DATE '2014-01-15' UNION ALL
  SELECT 1, DATE '2008-07-31' UNION ALL
  SELECT 5, DATE '2012-10-09' UNION ALL
  SELECT 2, DATE '2009-04-22'
),
oldest AS (
  SELECT id, created
  FROM accounts
  ORDER BY created, id
  LIMIT 3
)
SELECT id, created, DENSE_RANK() OVER (ORDER BY created) AS age_rank
FROM oldest
ORDER BY age_rank, id

This is Google’s own example, and we saw the effect it describes, 15 seconds against 2 on their table, at larger scale on ours. It does not work when the rank must reflect rows that the limit removes. See window functions.

Approximate counts

APPROX_COUNT_DISTINCT gave 3 to 5 times less slot-time than COUNT(DISTINCT) over about a million distinct values. The answer is approximate by design, but on a column with 997 distinct values it returned the exact number:

SELECT
  COUNT(DISTINCT MOD(i, 997)) AS exact_count,
  APPROX_COUNT_DISTINCT(MOD(i, 997)) AS approx_count
FROM UNNEST(GENERATE_ARRAY(1, 5000)) AS i

When the error is acceptable, as in a dashboard that shows “about 1.2M users”, use it. See approximate aggregation. Querylab.io hints at COUNT(DISTINCT x) and has a quick fix that replaces it with APPROX_COUNT_DISTINCT (code quality). APPROX_QUANTILES replaces NTILE the same way. Google notes the buckets are close but not identical: the remainder rows land in different places.

JavaScript UDFs

A JavaScript UDF starts a subprocess. For SUM(f(x)) over 10 million rows, the JavaScript version took 15.5 slot-seconds and the SQL UDF and the inline expression both took 0.6. A SQL UDF cost nothing extra here, and Google says the optimizer can optimize SQL UDF definitions. Use JavaScript only for logic SQL can’t express. See user-defined functions.

LIKE and regular expressions

LIKE was twice as fast as REGEXP_CONTAINS for a substring test. The difference is real but small in absolute terms, and the two give the same answer for a plain substring:

WITH pages AS (
  SELECT '/shop/checkout' AS url UNION ALL
  SELECT '/shop/cart' UNION ALL
  SELECT '/checkout/done' UNION ALL
  SELECT '/blog/checkout-tips' UNION ALL
  SELECT '/Checkout'
)
SELECT
  url,
  url LIKE '%checkout%' AS like_match,
  REGEXP_CONTAINS(url, r'checkout') AS regexp_match
FROM pages
ORDER BY url

Both are case-sensitive, so /Checkout matches neither. Use a regular expression when you need one. Google’s example pattern '.*test.*' was in the same range as the plain one.

Types in filters

Google says to use BOOL, INT64, FLOAT64 or DATE columns in WHERE clauses rather than strings. It held: the same row count found with k = 123456 took 0.48 slot-seconds and with ks = '123456' took 0.87, on a table where ks is the string form of k. All the bytes were read in both cases. The effect is roughly twice, which only matters on large scans. The same applies to join keys. See optimizing joins.

Sorting

Google says to put ORDER BY in the outermost query or in a window, because one in the middle of a query greatly affects performance. If you only need the top rows, add LIMIT: the plan then has SORT ... LIMIT 10 steps that keep only the top rows in each worker. A plain sort of 10 million rows was the slowest stage of its plan and raised the elapsed time from 3.4 to 11.4 seconds. Querylab.io warns about ORDER BY without LIMIT on a large table (code quality).

What we could not confirm

“Place the largest table first in a join.” Google’s guide says to order tables from largest to smallest and that this triggers a broadcast join. We joined a 10-million-row table with a 100,000-row table in both orders as an inner join and as a left join, and once as a right join. Every version produced the same one-stage plan, with a median slot-time between 1.0 and 1.7 seconds. The optimizer chooses the sides. See optimizing joins.

“Use temporary tables instead of a CTE that is referenced twice.” Google warns that a CTE used in several places may be evaluated each time. It was: reading the CTE twice cost 11.0 slot-seconds against 6.3 for reading it once. But writing it to a temporary table and reading that twice cost 16.5 slot-seconds and ran for 5.4 seconds against 1.0, because writing and reading the table cost more than the repeated aggregation did. A temp table pays off when the CTE is expensive and is read many times. It didn’t for one aggregate read twice. See temporary tables.

“Filter before the join.” Writing the filter in WHERE, in the ON clause or in a subquery before the join gave the same plan and the same bytes. BigQuery moves the filter itself. What helps is a filter that shrinks one side, and it helps through the plan, not through where you wrote it.

What does show up in the bytes

Primary and foreign key constraints are declared, not enforced, and the optimizer uses them. On a 10-million-row table joined to a 100,000-row one, a LEFT JOIN that selected only columns of the big table was dry-run at 160.8 MB when neither table had constraints, and 80 MB when the small table had PRIMARY KEY (k) NOT ENFORCED and the big one a matching FOREIGN KEY. BigQuery dropped the join. An INNER JOIN kept reading the foreign key column but stopped reading the small table. The cost of this is on you: if the data breaks the constraint, results can be wrong. See primary and foreign keys.

Materialized views are the other big one: a rewritten aggregation read 120 KB where the table scan read 14.4 MB.

How we measured

Each query ran with the cache off, on tables we created from GENERATE_ARRAY. We read totalSlotMs and the elapsed time from the job, and for the closest comparisons alternated the two versions so a slow minute hit both. The plan and INFORMATION_SCHEMA.JOBS_BY_PROJECT also show optimization_details, where Google records the history-based optimizations that change a query between runs. Your numbers will differ, in size and in sign for the borderline cases. The ones in the first table are large enough that they won’t flip. In Querylab.io the execution plan of a finished query shows slot-ms for each stage.