History Optimize

Optimize reads your job history for the last 7, 30 or 90 days and shows where the BigQuery money went, which queries could read less data for the same result, how slots would price against on-demand, and which queries got slower or cheaper over time.

Open Query history and switch Queries | Optimize in the tab header. Queries is the list of single jobs. Optimize reads the whole period at once and answers three questions: where did the money go, what can be changed without changing the result, and what changed over time.

Pick 7 days, 30 days or 90 days, and Mine or Everyone. Nothing runs until you click Run.

Where the numbers come from

Optimize runs one aggregate query on INFORMATION_SCHEMA.JOBS_BY_USER (or JOBS_BY_PROJECT with Everyone), grouped by BigQuery’s query hash, so re-runs of the same query count together. Literal values and comments do not change the hash. It sees every tool that used the project: Querylab.io, the console, dbt, scheduled queries.

• The query is dry-run first. You see its size and cost, and you approve it when it is over your auto-run limit. The read is billed like any query, and a 7 day report reads more history than its 7 days (see Got worse below).
• Only finished query jobs count. For a script, its statements count and the script job itself does not, so slot time is not counted twice.
• Paid is what on-demand jobs were billed by the byte. Jobs that ran on a reservation are billed by slot time, so they are shown apart and priced as an estimate.
• Failed queries are free on-demand and show as failed. Cancelled ones can bill and are counted.
• A query on a table with row-level security reports no billed bytes. Optimize counts those as 0 and says that the total is a lower bound.
• Spend per day uses your time zone. A bar opens that day’s queries in Queries.

Every figure has its math under Show the math. An estimate is labelled as one.

Which regions are read

INFORMATION_SCHEMA.JOBS is per region, so Optimize needs to know where your jobs ran. It uses the project’s region, the regions of its datasets and the regions of recent jobs. Check all regions does one cheap count per BigQuery region (45 of them) under one approval, to find every region that has jobs. The answer is remembered for 7 days. Use known regions only skips the check and reads the regions the clues show.

The report lists the regions it read and the regions it could not check, for example a region your organization does not allow. A failed region is left out of every number and the report says it is partial. Each region is priced with its own prices.

Quick fixes

A quick fix is a rewrite of a query that returns the same rows and reads less data, or lets repeats come from the cache. Each card shows the SQL diff, the bytes read before and after from two dry runs, the runs and cost in the period, and Show the math. Open fixed SQL in a new tab opens the fixed query. Nothing runs until you run it. Not now takes the fix out of every total for this session, with an undo.

The detectors read the SQL with the Prism parser, not with patterns. There are two kinds.

A filter that stops partition pruning. BigQuery cannot prune a partitioned table when the partition column is changed before it is compared:

WHERE refresh_date + INTERVAL 7 DAY > CURRENT_DATE()

The fix moves the arithmetic to the other side, so the column is compared as it is:

WHERE refresh_date > DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

The rewrite is exact only for units of a fixed length, so it applies to DAY and WEEK on dates, up to DAY on timestamps, and up to WEEK on datetimes. MONTH, QUARTER and YEAR are not rewritten, because a month has no fixed length. When the other side of the comparison is a subquery, the finding is reported without a fix. The saving is the difference of the two dry runs, so a filter that did not change what BigQuery reads shows no saving.

A function that blocks the cache. BigQuery never caches a query that calls CURRENT_DATE(), CURRENT_TIMESTAMP() and similar functions. The fix replaces the call with a query parameter and fills in the value when the tab opens, for example today minus 7 days:

WHERE refresh_date >= @cutoff_date

-- querylab:param cutoff_date DATE = '2026-10-23'

The value is set once, when the tab opens, and stays fixed after that. Saved as it is, the query keeps reading from that date. A scheduled query, a dbt model or anything else that runs it has to set @cutoff_date fresh on every run, or the saving turns into a query that reads more and returns different rows.

The fix is all or nothing: if one call cannot be replaced, nothing is rewritten. A query on a wildcard table, one that uses RAND, and a call in the select list, GROUP BY, ORDER BY or FROM are left as they are, because there the value is data. The saving counts the runs beyond one per day. It is an estimate, it counts on-demand runs only, scheduled runs are left out, and the repeat only comes from the cache when the data has not changed.

Why SELECT * has no fix. BigQuery already prunes the columns a query does not use when SELECT * sits in a CTE or a subquery, so naming the columns there saves nothing. We measured the same bytes read both ways. Only a final SELECT * reads every column. Optimize notes it with the column count and gives no saving, because there is no honest figure to give.

Two fixes on the same query overlap, so the larger one counts in the total. The total at the top, Could save about $X a month, adds one line per query. A saving under one cent shows as <$0.01, not $0.00.

Slots against on-demand

Pricing model compares what you paid on-demand with the slots of your edition. Jobs that already ran on a reservation are left out, because they were not paid by the byte. On slots the cost is an estimate with a range, because an autoscaling reservation bills more than the slot time a query used. It scales in steps of 50 slots and holds a scale-up for at least one minute.

• The low figure assumes queries share scale-ups.
• The high figure assumes each query pays for its own one-minute minimum of 50 slots (3,000,000 slot milliseconds).
• A saving is claimed only against the high figure, summed over the queries that win: Slots would likely save about $X a month (estimate). When slots could cost much more than on-demand, you get a plain sentence instead: slots could cost less or much more, depending on how many queries run at the same time.

These are estimates, not bounds. A query that steps up to more slots holds each new level for a minute, and billing follows the slots scaled, not the slots used. Slot time and duration do not show those steps, so a real bill can be above the high figure.

Fluid scaling removes the one-minute minimum and bills per second. It does not change the 50-slot steps. It is opt-in per reservation, in the project that owns the reservations. The estimate with it on is the slot time plus 50 slots for each second the query ran. When slots only look cheaper with fluid scaling on, Optimize says so and shows the statement to turn it on.

The option is one list for the whole project and location, so setting it for one reservation without the others would switch it off for them. When Optimize can read the list, the statement carries it:

ALTER PROJECT `admin-project`
SET OPTIONS (`region-us.preflight_fluid_autoscaling_reservations` = ["my-reservation", "another-reservation"]);

When it cannot read the list, it shows the read first, and the statement has a comment where the names it returns go:

SELECT option_name, option_type, option_value
FROM `region-us`.INFORMATION_SCHEMA.PROJECT_OPTIONS
WHERE option_name = 'preflight_fluid_autoscaling_reservations'

The admin project and the location come from your project’s reservation. If your project already runs on a reservation, Optimize reads whether fluid scaling is on for it and uses the matching figure. If it cannot read the setting, it shows both figures and does not guess. This matters most for short ad hoc queries, where the minute is most of the bill.

Over time

Two parts of the report compare a query with its own past. A query is the group of runs with one hash, so a query rewritten with a new structure is a new query and has no past to compare with. A fix applied through a quick fix opens a new tab with different SQL, so it is not matched to the old query either. The comparison finds a drop within one query, for example from a changed filter value or a table change.

Only finished runs that were not cache hits count. Each side is summarised by its middle value. This needs the SQL source: the Jobs API source loads too few days to cover 30 days before the last 7 runs, so it rarely shows anything here. A baseline of zero has no ratio, so it is never listed.

Got worse (Speed) compares the last 7 runs of a query with its runs in the 30 days before the oldest of those 7. It needs the 7 recent runs and at least 7 earlier ones, and all 30 earlier days must have been read. A query shows when its middle run takes at least 1.5 times as long and at least 5 seconds more, or bills at least 1.5 times as much and at least 1 GB more. A query with fewer runs is not listed, and a figure that is missing is not read as zero. When the SQL text changed between the two sets of runs, the row says so and shows the first line that differs.

Your fix is working shows a query whose last 7 runs read at most half as many bytes as before and at least 1 GB less per run, with every one of the 7 below the older middle value. It names the date, the bytes saved per run and, for on-demand runs, the money a month at the pace of those runs. It is measured from the runs, not estimated. The date is marked on Spend per day. A query with runs on a reservation, in either set, shows the bytes but no money figure, and its bytes are called read, not billed.

Your limits

Under the chart, Your limits shows today, this week and this month against the budgets you and your organization set. It counts queries run in Querylab.io only, so it can be lower than the spend above, which includes dbt and the console. See Cost limits.

Export CSV under Where the money went saves the breakdown by query, table, user or source.

Related: Query history, Auto routing, Pricing comparison, On-demand vs capacity pricing.