Get query performance insights

The execution graph for a query is a visual representation of the steps that BigQuery takes to execute the query. This document describes how to use the query execution graph to diagnose query performance issues, and to see query performance insights.

BigQuery offers strong query performance, but it is also a complex distributed system with many internal and external factors that can affect query speed. The declarative nature of SQL can also hide the complexity of query execution. This means that when your queries are running slower than anticipated, or slower than prior runs, understanding what happened can be a challenge.

The query execution graph provides a dynamic graphical interface for inspecting the query plan and query performance details. You can review the query execution graph for any running or completed query.

You can also use the query execution graph to get performance insights for queries. Performance insights provide best-effort suggestions to help you improve query performance. Since query performance is multi-faceted, performance insights might only provide a partial picture of the overall query performance.

Required permissions

To use the query execution graph, you must have the following permissions:

bigquery.jobs.get
bigquery.jobs.listAll

These permissions are available through the following BigQuery predefined Identity and Access Management (IAM) roles:

roles/bigquery.admin
roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer

Execution graph structure

The query execution graph provides a graphical view of the query plan in the console. Each box represents a stage in the query plan such as the following:

Input: Reading data from a table or selecting specific columns
Join: Merging data from two tables based on the JOIN condition
Aggregate: Performing calculations such as SUM
Sort: Ordering the results

Stages are made up of steps that describe the individual operations that each worker within a stage executes. You can click a stage to open it and view its steps. Stages also include relative and absolute timing information. Stage names summarize the steps they perform. For example, a stage with join in its name means that the principal step in the stage is a JOIN operation. Stage names that have + at the end mean that they perform additional important steps. For example, a stage with JOIN+ in its name means that the stage performs a join operation and other important steps.

The lines that connect stages represent the exchange of intermediary data between stages. BigQuery stores the intermediary data in shuffle memory while stages are executing. Numbers on the edges indicate the estimated number of rows exchanged between stages. Shuffle memory quota is correlated with the number of slots allocated to the account. If the shuffle quota is exceeded, shuffle memory can spill to disk and cause query performance to slow dramatically.

View query performance insights

Console

Follow these steps to see query performance insights:

Open the BigQuery page in the Google Cloud console.

Go to the BigQuery page
In the left pane, click Explorer:

If you don't see the left pane, click Expand left pane to open the pane.
In the Explorer pane, click Job history.
Click either Personal History or Project History.
In the list of jobs, identify the query job that interests you. Click Actions, and choose View job in editor.
Select the Execution graph tab to see a graphical representation of each stage of the query:

To determine if a query stage has performance insights, look at the icon it displays. Stages that have an information icon have performance insights. Stages that have a check icon don't.
Click a stage to open the stage details pane, where you can see the following information:
- Query plan information for the stage.
- The steps executed in the stage.
- Any applicable performance insights.
Optional: If you are inspecting a running query, click Sync to update the execution graph so that it reflects the query's current status.
Optional: To highlight the top stages by stage duration on the graph, click Highlight top stages by duration.
Optional: To highlight the top stages by slot time used on the graph, click Highlight top stages by processing.
Optional: To include shuffle redistribution stages on the graph, click Show shuffle redistribution stages.

Use this option to show the repartition and coalesce stages that are hidden in the default execution graph.

Repartition and coalesce stages are introduced while the query is running, and are used to improve data distribution across the workers processing the query. Since these stages are not related to your query text, they are hidden to simplify the query plan that is displayed.

For any query that has performance regression issues, performance insights are also displayed on the Job Information tab for the query:

The job information tab.

SQL

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, enter the following statement:

SELECT
  `bigquery-public-data`.persistent_udfs.job_url(
    project_id || ':us.' || job_id) AS job_url,
  query_info.performance_insights
FROM
  `region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) >= CURRENT_DATE - 30 -- scan 30 days of query history
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
  AND statement_type != 'SCRIPT'
  AND EXISTS ( -- Only include queries which had performance insights
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_standalone_insights
    )
    WHERE
      slot_contention
      OR insufficient_shuffle_quota
      OR bi_engine_reasons IS NOT NULL
      OR high_cardinality_joins IS NOT NULL
      OR partition_skew IS NOT NULL
    UNION ALL
    SELECT 1
    FROM UNNEST(
      query_info.performance_insights.stage_performance_change_insights
    )
    WHERE input_data_change.records_read_diff_percentage