Database Reference
In-Depth Information
Encouraging a Limit
In the first release of the web application, the query template in the
compose box did not include a trailing limit. We quickly discovered
users were hitting this error, so the default template was modified to
include a limit of 1,000 rows. Unfortunately, no such safety net exists in
the command-line client, so users still hit this error when testing with
that tool.
Arguably, the aim of most queries is to turn “Big Data” into “Small
Data”—the loose definition of small data being something humans can
usefully consume. Even if the data is feeding visualizations, this limit is
usually not more than a couple of thousand rows. In many cases a “Response
too large” error is an indication that a query is not sufficiently selective
or requires further aggregation. The error is triggered when the size of the
result exceeds approximately 128 MB. The specific value is not particularly
useful because it is likely to change over time, and in any case it is not easy
to estimate the size of your results before running the query. A simpler rule
of thumb is to aim for query results that return less than 10,000 rows.
When developing complex queries that involve subqueries, it is often useful
to look at the result of the subquery to ensure it is producing the results
you intended. Often these subqueries generate large results, which the final
outer query will summarize into a manageable number of rows. Simply slap
a
LIMIT
clause at the end of the subquery to test it independently; just
remember to drop the clause when pasting it into the final query. Further,
if you have an
ORDER BY
clause and need only the top or bottom
N
results,
then adding a
LIMIT
N
clause will likely significantly speed up your query
execution.
Finally, there are times when you do want to execute a query that is going to
return a large result. Possible use cases include:
• The result will be used as a source in future queries.
• Data will be exported for external processing.
• The table is being updated to fix bad data.
