LIMIT hints

A LIMIT usually caps the rows you get back and costs the same. On a clustered table it can also cut the bytes BigQuery reads. Querylab.io checks each query and tells you when that is the case, and only then.

When a LIMIT saves money

BigQuery normally applies LIMIT after the scan. The query reads every block it would read anyway, and you pay for all of it. On a clustered table the data is stored in clustering order, so a query that only needs the first rows it finds can stop reading early.

Querylab.io shows the hint only when all of these hold:

• The query is one SELECT that reads one stored table, and that table is clustered. A name defined in a WITH clause is a CTE, not a stored table, so it gets no hint.
• It has no ORDER BY, GROUP BY or HAVING, aggregate function such as SUM or COUNT, DISTINCT, window function or QUALIFY.
• It has no JOIN, no comma join, and no UNION or other set operation.
• It reads the table directly, not through a subquery, UNNEST or table function, and it has no subquery in the SELECT list or WHERE, such as IN (SELECT ...), EXISTS or a scalar subquery.
• It is not written in pipe syntax or as a parenthesized query.
• BigQuery’s dry run does not return an exact estimate. An exact estimate means the bytes read are already fixed, so no LIMIT can lower them, and the hint goes away once the estimate arrives.
• For a suggestion, it has no LIMIT of its own yet. A query that already has one is covered in When the query already has a LIMIT.

SELECT title, views
FROM `bigquery-public-data.wikipedia.pageviews_2024`
WHERE datehour = '2024-01-01 00:00:00'

If Querylab.io can’t load the table’s schema, it says nothing rather than guess.

Adding the LIMIT

Click the status bar text. The Insights panel opens on its cost view with a card that explains the hint:

Add LIMIT 1000 writes the clause at the end of the query in the editor. Ctrl+Z removes it. If you edited the query after the card appeared and it no longer qualifies, nothing is inserted and the card says so.

When the query already has a LIMIT

If you wrote the LIMIT yourself and the other conditions hold, your LIMIT is what can stop the scan early. Querylab.io says so and changes nothing. It never adds a second LIMIT, which BigQuery rejects, and it never replaces yours.

The status bar reads LIMIT 100 may read less, with your row count. When the count isn’t a plain number, for example LIMIT @n, it reads This LIMIT may read less. Hover it for the reason: the table is clustered, the query has no sort, aggregation or join, and the estimate can be higher than the bill.

Click the text to open the same cost card. Its first line reads Your LIMIT 100 can stop the scan early, it names the clustering columns, and it has no Add LIMIT button. LIMIT 100 OFFSET 20 counts too, because BigQuery still stops after 120 rows.

After the run, the card shows the read-versus-estimate line described in the next section.

This text shows in Suggest it and Add it automatically. Do nothing shows nothing. A LIMIT under an ORDER BY, on an unclustered table or over a JOIN gets no text, because it only caps the rows. A negative LIMIT gets none either, because BigQuery rejects it.

Why the estimate doesn’t drop

The cost in the status bar comes from a dry run, and a dry run can’t see an early stop. The same query estimates the same bytes with and without the LIMIT. That’s why the hint shows no percentage before the run.

After the run, the card compares the bytes the query read with the estimate, for example This query read 15.7 MB; the estimate said 5.47 GB. It shows this line only when the query read less than estimated, and not for a result served from the query cache. It belongs to the query you ran, so it disappears when you edit the query. This applies to a LIMIT Querylab.io added and to one you wrote.

Choosing a mode

Settings → Limits & Costs → Default LIMIT → When a LIMIT would reduce the scan:

Rows sets the number used, 1000 by default.

Related: cost limits stop a query that would scan too much, and TABLESAMPLE cuts the cost of queries that a LIMIT can’t help.