Introduction to optimizing query performance
Sometimes, queries run more slowly than you would like. In general, queries that do less work perform better. They run faster and consume fewer resources, which can result in lower costs and fewer failures. This document provides an overview of optimization techniques that can improve query performance in BigQuery.
Evaluating query performance in BigQuery involves several factors:
- Input data and data sources (I/O): How many bytes does your query read?
- Communication between nodes (shuffling): How many bytes does your query pass to the next stage? How many bytes does your query pass to each slot?
- Computation: How much CPU work does your query require?
- Outputs (materialization): How many bytes does your query write?
- Query anti-patterns: Are your queries following SQL best practices?
Many of these questions are answered in a query plan that BigQuery generates each time you run a query in BigQuery. The query plan shows execution statistics such as bytes read and slot time consumed. It also shows the different stages of execution, which can help you diagnose and improve query performance. For more information, including an example of diagnostics for a query, see Query plan and timeline.
As with any systems, optimizing for performance sometimes involves tradeoffs. For example, using advanced SQL syntax can sometimes introduce complexity and reduce the queries' understandability for people who aren't SQL experts. Spending time on micro-optimizations for noncritical workloads could also divert resources away from building new features for your applications or from making optimizations that are more impactful. Therefore, to help you achieve the highest possible return on investment, we recommend that you focus your optimizations on the workloads that matter most to your data analytics pipelines.
Even an optimized query may run slowly due to slot contention. For example, if there are 10,000 slots available to 6 projects, not all of them can use 2,000 slots. This might cause the queries or jobs to slow down. If your queries cannot be optimized further, consider using reservations.
To evaluate whether specific queries are problematic, you can use Cloud Monitoring to monitor how your BigQuery jobs consume resources over time. If you identify a slow or resource-intensive query, you can focus your performance optimizations on that query.
Capacity and concurrency
BigQuery breaks down the computational capacity required to execute SQL queries into units called slots. BigQuery then automatically calculates how many slots each query requires, depending on the query's size and complexity.
BigQuery automatically manages the slot quota that your running queries share, based on customer history, usage, and spending. Most users find the default slot capacity for each project more than sufficient. Access to more slots doesn't guarantee faster performance for a query. However, a larger pool of slots might improve performance of large or complex queries, as well as performance of highly concurrent workloads. To further improve query performance, consider the benefits of purchasing more reserved slots, in addition to optimizing your data model and queries.
BigQuery offers two pricing models for queries: on-demand pricing and flat-rate pricing. On-demand pricing is based on the amount of data processed by each query you run. Flat-rate pricing is suitable if you require consistent monthly expenditures on analytics spending. When you enroll in flat-rate pricing, you purchase dedicated query processing capacity that is measured in BigQuery slots. The cost of all bytes processed is included in the monthly flat-rate price. If your queries exceed your flat-rate capacity, they are queued until your flat-rate resources become available.
Query plan and timeline
BigQuery in the Google Cloud console
lets you visually inspect the
query plan and timeline
for your queries. You can use the
jobs.get API method to retrieve the query
plan and timeline information. You might also be interested in trying out
an open source tool that visually represents the flow of execution stages in a
When BigQuery executes a query job, it converts the declarative SQL statement into a graph of execution. This graph is broken up into a series of query stages, which themselves are composed of more granular sets of execution steps. BigQuery uses a heavily distributed parallel architecture to run these queries. The BigQuery stages model the units of work that many potential workers might execute in parallel. Stages communicate with one another through a fast, distributed shuffle architecture.
In addition to the query plan, query jobs also expose a timeline of execution. This timeline provides an accounting of units of work completed, pending, and active within query workers. A query might have multiple stages with active workers simultaneously, so the timeline is intended to show overall progress of the query.
To estimate how computationally expensive a query is, you can look at the total number of slot seconds the query consumes. The lower the number of slot seconds, the better, because it means more resources are available to other queries running in the same project at the same time.
The query plan and timeline statistics can help you understand how
BigQuery executes queries and if certain stages dominate resource
utilization. For example, a
JOIN stage that generates far more output rows than
input rows might indicate an opportunity to filter earlier in the query.
However, the managed nature of the service limits whether some details are
directly actionable. For best practices and techniques to improve query
execution and performance, see
Optimize query computation.
- Learn how to troubleshoot query execution issues using the BigQuery audit logs.
- Learn other cost-controlling techniques for BigQuery.
- Learn how to monitor your BigQuery usage using the BigQuery System Tables Reports.