Use history-based optimizations

This guide describes how to enable, disable, and analyze history-basedoptimizations for queries.

About history-based optimizations

History-based optimizations use information from already completed executionsof similar queries to apply additional optimizations and further improvequery performance such as slot time consumed and query latency. For example,when you apply history-based optimization, the first query execution might take60 seconds, but the second query execution might take only 30 seconds if ahistory-based optimization was identified. This process continues until thereare no additional optimizations to add.

The following is an example of how history-based optimizations work withBigQuery:

History-based optimizations are only applied when there is high confidence thatthere will be a beneficial impact to the query performance. In addition, when anoptimization does not significantly improve query performance, that optimizationis revoked and not used in future executions of that query.

Roles and permissions

• To enable or disable history-based optimizations, you must have therequired permissions to create BigQuerydefault configurations, and then you must use theALTER PROJECT statementto enable history-based optimizations. Once you've enabledhistory-based optimizations, all jobs in that project usehistory-based optimizations, regardless of which user created the job.To learn more about required permissions for default configurations, seeRequired permissionsfor default configurations. To enable history-based optimizations, seeEnable history-based optimizations.

• To review the history-based optimizations for a job using theINFORMATION_SCHEMA.JOBS view, you must have the required role. For moreinformation, seeRequired roleforINFORMATION_SCHEMA.JOBS view.

Enable history-based optimizations

History-based optimizations are enabled by default. If history-basedoptimizations have been disabled for a project or organization, you can manuallyre-enable history-based optimizations by including thedefault_query_optimizer_options = 'adaptive=on' parameter in yourALTER PROJECTorALTER ORGANIZATIONstatement. For example:

ALTERPROJECTPROJECT_NAMESETOPTIONS(`region-LOCATION.default_query_optimizer_options`='adaptive=on');

Replace the following:

• PROJECT_NAME: the name of the project
• LOCATION: the location in which jobs should attempt touse history-based optimizations

To disable history-based optimizations in a project, include thedefault_query_optimizer_options = 'adaptive=off' parameter in theALTER PROJECTorALTER ORGANIZATIONstatement. For example:

ALTERPROJECTPROJECT_NAMESETOPTIONS(`region-LOCATION.default_query_optimizer_options`='adaptive=off');

Replace the following:

• PROJECT_NAME: the name of the project
• LOCATION: the location in which jobs should notattempt to use history-based optimizations

SELECTjob_id,query_info.optimization_detailsFROM`PROJECT_NAME.region-LOCATION`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHEREjob_id='sample_job'LIMIT1;

-- 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"                       | |            |     },                                                          | |            |   ]                                                             | |            | }                                                               | *------------+-----------------------------------------------------------------*/

{"jobReference":{"projectId":"myProject","jobId":"sample_job"}}

-- 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"}]}}}}}

Estimate impact of history-based optimizations

To estimate the impact of history-based optimizations, you can use the following sample SQL query to identify project queries with the greatest estimated improvement to execution time.

WITHjobsAS(SELECT*,query_info.query_hashes.normalized_literalsASquery_hash,TIMESTAMP_DIFF(end_time,start_time,MILLISECOND)ASelapsed_ms,IFNULL(ARRAY_LENGTH(JSON_QUERY_ARRAY(query_info.optimization_details.optimizations)) >0,FALSE)AShas_history_based_optimization,FROMregion-LOCATION.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHEREEXTRACT(DATEFROMcreation_time) >DATE_SUB(CURRENT_DATE(),INTERVAL30DAY)),most_recent_jobs_without_history_based_optimizationsAS(SELECT*FROMjobsWHERENOThas_history_based_optimizationQUALIFYROW_NUMBER()OVER(PARTITIONBYquery_hashORDERBYend_timeDESC)=1)SELECTjob.job_id,100*SAFE_DIVIDE(original_job.elapsed_ms-job.elapsed_ms,original_job.elapsed_ms)ASpercent_execution_time_saved,job.elapsed_msASnew_elapsed_ms,original_job.elapsed_msASoriginal_elapsed_ms,FROMjobsASjobINNERJOINmost_recent_jobs_without_history_based_optimizationsASoriginal_jobUSING(query_hash)WHEREjob.has_history_based_optimizationANDoriginal_job.end_time <job.start_timeORDERBYpercent_execution_time_savedDESCLIMIT10;

The result of the preceding query is similar to the following if history-based optimizations were applied:

/*--------------+------------------------------+------------------+-----------------------*   |    job_id    | percent_execution_time_saved | new_execution_ms | original_execution_ms |   +--------------+------------------------------+------------------+-----------------------+   | sample_job1  |           67.806850186245114 |             7087 |                 22014 |   | sample_job2  |           66.485800412501987 |            10562 |                 31515 |   | sample_job3  |           63.285605271764254 |            97668 |                266021 |   | sample_job4  |           61.134141726887904 |           923384 |               2375823 |   | sample_job5  |           55.381272089713754 |          1060062 |               2375823 |   | sample_job6  |           45.396943168036479 |          2324071 |               4256302 |   | sample_job7  |           38.227031526376024 |            17811 |                 28833 |   | sample_job8  |           33.826608962725111 |            66360 |                100282 |   | sample_job9  |           32.087813758311604 |            44020 |                 64819 |   | sample_job10 |           28.356416319483539 |            19088 |                 26643 |   *--------------+------------------------------+------------------+-----------------------*/

The results of this query is only an estimation of history-based optimizationimpact. Many factors can influence query performance, including but not limitedto slot availability, change in data over time, view or UDF definitions, anddifferences in query parameter values.

If the result of this sample query is empty, then either no jobs have usedhistory-based optimizations, or all queries were optimized more than 30 daysago.

This query can be applied to other query performance metrics such astotal_slot_ms andtotal_bytes_billed. For more information, see the schemaforINFORMATION_SCHEMA.JOBS.