History based optimization [Preview]

History-based optimization uses runtime statistics from previous query executions to improve execution plans for similar queries without requiring table statistics.

Overview

History Based Optimization (HBO) accelerates query processing by reusing historical runtime statistics collected from previous query runs. Impala collects runtime statistics during query processing and stores them in the HBO cache. By default, the in-memory cache is maintained per coordinator and is lost when the coordinator restarts. During subsequent query planning, the planner reads the stored cardinality statistics from the HBO cache and applies them to generate an optimized query plan.

Observability updates

You can verify whether a query uses HBO statistics by reviewing the query profile or EXPLAIN output. If cardinality statistics are read from the HBO cache, the EXPLAIN output or the query plan in the profile displays the "from HBO" annotation. Extended profile updates provide additional details regarding the applied HBO statistics

EXPLAIN output examples

When HBO is enabled and statistics are found in the cache, the EXPLAIN output shows "from HBO" annotations in the cardinality estimates. The following example shows a join query where HBO provides cardinality for both scan nodes and the join operation:

PLAN-ROOT SINK
|
02:HASH JOIN [INNER JOIN, BROADCAST]
|  hash predicates: b.id = a.id
|  row-size=29B cardinality=31 (from HBO)
|
|--04:EXCHANGE [BROADCAST]
|  |
|  00:SCAN HDFS [functional.alltypes a]
|     partition predicates: a.`year` = 2010, a.`month` = 1
|     row-size=8B cardinality=31 (from HBO)
|
01:SCAN HDFS [functional.alltypes b]
   partition predicates: b.`year` = 2010
   row-size=21B cardinality=365 (from HBO)

In this example, HBO provides cardinality estimates of 31 rows for the first scan and 365 rows for the second scan.

Enabling history based optimization

Configure History Based Optimization query options in Impala to store runtime statistics and apply historical cardinality data during query planning.

Prerequisites: You must have access to the Impala shell or a supported query editor.

  • Enable the STORE_HBO_STATS query option to populate the HBO cache with runtime query statistics. The statistics are stored asynchronously after the query finishes, so enabling this option does not add significant query latency: SET STORE_HBO_STATS=true;
  • Enable the USE_HBO_STATS query option to instruct the planner to read and apply stored statistics from the HBO cache: SET USE_HBO_STATS=true;

History based optimization flags

History Based Optimization startup flags specify the similarity threshold, maximum stored runs, and memory cache size for the Impala daemon.

The following table describes the History Based Optimization startup flags for the Impala daemon (impalad):
Table 1. HBO flags
Flag Default Value Description
hbo_similarity_threshold 0.1 (10%) Specifies the similarity threshold value used to match queries against historical entries in the HBO cache. The threshold compares scan input statistics, and hash keys identify plan-node subtrees rather than whole queries.
hbo_max_runs_per_key 100 Specifies the maximum number of historical runs stored per hash key in the HBO cache. When this limit is exceeded, the oldest run is evicted.
hbo_in_memory_backend_cache_size_bytes 1 GB Specifies the maximum memory size, in bytes, allocated for the in-memory backend HBO cache.