Use history-based optimizations
For support during the preview, email bigquery-history-based-optimization-support@google.com.
This guide describes how to enable, disable, and analyze history-based optimizations for queries.
About history-based optimizations
History-based optimizations use information from already completed executions of similar queries to apply additional optimizations and further improve query performance such as slot time consumed and query latency. For example, when you apply history-based optimization, the first query execution might take 60 seconds, but the second query execution might take only 30 seconds if a history-based optimization was identified. This process continues until there are no additional optimizations to add.
The following is an example of how history-based optimizations work with BigQuery:
Execution count | Query slot time consumed | Notes |
---|---|---|
1 | 60 | Original execution. |
2 | 30 | First history based-optimization applied. |
3 | 20 | Second history based-optimization applied. |
4 | 21 | No additional history based-optimizations to apply. |
5 | 19 | No additional history based-optimizations to apply. |
6 | 20 | No additional history based-optimizations to apply. |
History-based optimizations are only applied when there is high confidence that there will be a beneficial impact to the query performance. In addition, when an optimization does not significantly improve query performance, that optimization is revoked and not used in future executions of that query.
Enable history-based optimizations
To use history-based optimizations in a project, include the following parameter
in the
ALTER PROJECT
or
ALTER ORGANIZATION
statement: default_query_optimizer_options = 'adaptive=on'
Example:
ALTER PROJECT `user_project` SET OPTIONS ( `region-us.default_query_optimizer_options` = 'adaptive=on' );
Disable history-based optimizations
To disable history-based optimizations in a project, include the
default_query_optimizer_options = 'adaptive=off'
parameter in the
ALTER PROJECT
or
ALTER ORGANIZATION
statement.
Example:
ALTER PROJECT `user_project` SET OPTIONS ( `region-us.default_query_optimizer_options` = 'adaptive=off' );
Review history-based optimizations for a job
To review the history-based optimizations for a job, you can use a SQL query or a REST API method call.
SQL
You can use a query to get the history-based optimizations for a job.
The query must include INFORMATION_SCHEMA.JOBS_BY_PROJECT
and the query_info.optimization_details
column name.
In the following example, the optimization details are returned for a job
called sample_job
. If no history-based optimizations were applied, NULL
is
produced for optimization_details
:
SELECT
job_id,
query_info.optimization_details
FROM `project_name.region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'sample_job'
LIMIT 1;
The results look similar to the following:
-- The JSON in optimization_details has been formatted for readability.
/*------------+-----------------------------------------------------------------*
| job_id | optimization_details |
+------------+-----------------------------------------------------------------+
| sample_job | { |
| | "optimizations": [ |
| | { |
| | "semi_join_reduction": "web_sales.web_date,RIGHT" |
| | }, |
| | { |
| | "semi_join_reduction": "catalog_sales.catalog_date,RIGHT" |
| | }, |
| | { |
| | "semi_join_reduction": "store_sales.store_date,RIGHT" |
| | }, |
| | { |
| | "join_commutation": "web_returns.web_item" |
| | }, |
| | { |
| | "parallelism_adjustment": "applied" |
| | }, |
| | ] |
| | } |
*------------+-----------------------------------------------------------------*/
API
To get the optimization details for a job, you can call the
jobs.get
method.
In the following example, the jobs.get
method returns the optimization details
(optimizationDetails
)
in the full response:
{
"jobReference": {
"projectId": "myProject",
"jobId": "sample_job"
}
}
The results look similar to the following:
-- The unrelated parts in the full response have been removed.
{
"jobReference": {
"projectId": "myProject",
"jobId": "sample_job",
"location": "US"
},
"statistics": {
"query": {
"queryInfo": {
"optimizationDetails": {
"optimizations": [
{
"semi_join_reduction": "web_sales.web_date,RIGHT"
},
{
"semi_join_reduction": "catalog_sales.catalog_date,RIGHT"
},
{
"semi_join_reduction": "store_sales.store_date,RIGHT"
},
{
"join_commutation": "web_returns.web_item"
},
{
"parallelism_adjustment": "applied"
}
]
}
}
}
}
}
Roles and permissions
To opt in to history-based optimizations, you must have the required permissions to create BigQuery default configurations, and then you must use the
ALTER PROJECT
statement to enable history-based optimizations. Once you've enabled history-based optimizations, all jobs in that project use history-based optimizations, regardless of which user created the job. To learn more about required permissions for default configurations, see Required permissions for default configurations. To enable history-based optimizations, see Enable history-based optimizations.To review the history-based optimizations for a job using the
INFORMATION_SCHEMA.JOBS
view, you must have the required role. For more information, see Required role forINFORMATION_SCHEMA.JOBS
view.