HBO: Optimize SQL with performance history-Hologres(Hologres)-阿里云帮助中心

History-Based Optimization (HBO) is a query optimization technique available in Hologres V4.0 and later. The static optimizer generates execution plans before a query runs, relying on table statistics that may be stale or absent. When those estimates are wrong — for example, after data volume spikes or distribution shifts — the resulting plan can be suboptimal. HBO corrects this by recording real execution statistics and feeding those insights back into subsequent query plans, reducing resource consumption and improving performance without manual tuning.

HBO currently applies two optimizations:

Hash table sizing — Sets the initial hash table size based on historical row counts, avoiding costly resize operations at runtime.
Multi-stage aggregation — Decides whether to apply pre-aggregation based on historical compression ratios, eliminating unnecessary aggregation steps when they would degrade performance.

HBO is enabled by default in V4.0 and later.

Enable and disable HBO

When HBO is enabled, the engine automatically collects historical execution statistics that subsequent queries use.

To disable HBO, run the appropriate GUC command:

-- Disable at the session level
SET hg_adaptive_execution_enable_history_based_optimization = off;

-- Disable at the DB level. Takes effect for new connections.
ALTER DATABASE <db_name> SET hg_adaptive_execution_enable_history_based_optimization = off;

Prerequisites for HBO to take effect

All three conditions must be met for a query to use HBO:

Row count threshold — The estimated row count for any table in the execution plan exceeds 100,000 (rows > 100,000), or a table has no statistics and its estimated row count defaults to 1,000 (rows = 1,000). To adjust the threshold:
Note
Do not set this threshold below 100,000. A low threshold causes HBO to sample frequently, adding framework overhead that can degrade performance.
```
-- Session level
SET hg_adaptive_execution_enable_hbo_min_estimated_scan_rows = <num>;

-- DB level. Takes effect for new connections.
ALTER DATABASE <db_name> SET hg_adaptive_execution_enable_hbo_min_estimated_scan_rows = <num>;
```
Hash Agg node — The execution plan must contain a Hash Agg node.
Historical statistics available — The query must have prior execution data that meets both of the following:
- The query has run at least once within the last 48 hours.
- The total execution time of the SQL statement exceeds 100 ms.

How HBO works

Hash table sizing

When a Hash Agg operation runs, the optimizer cannot know the exact output row count in advance. If the initial hash table is too large, memory is wasted; if it is too small, the table must resize multiple times during execution.

HBO addresses this by using the historical hash table row count as the initial size for the current execution. This optimization applies when the historical hash table row count exceeds 100,000 and the hash table size differs from the number of aggregation output rows.

To adjust the hit threshold for this optimization:

-- Session level
SET hg_adaptive_execution_hbo_generate_advice_hash_table_size_low_bound = <num>;

-- DB level. Takes effect for new connections.
ALTER DATABASE <db_name> SET hg_adaptive_execution_hbo_generate_advice_hash_table_size_low_bound = <num>;

Note

Do not set this threshold below 100,000. For small hash tables, the performance gain is marginal and the HBO framework overhead can outweigh it.

Multi-stage aggregation

Multi-stage aggregation pre-compresses data before redistribution, significantly reducing data transmission overhead. However, if the compression ratio is low, the repeated computation can degrade performance rather than improve it.

HBO uses the historical data compression ratio to decide whether to apply multi-stage aggregation:

High compression ratio — If the total number of rows for aggregation is large and the compression ratio is high, HBO forces multi-stage aggregation.
Low compression ratio — Removes the redundant pre-aggregation step and uses partial aggregation instead.

No manual configuration is required. The optimizer determines this adaptively.

Verify HBO is working

After a query runs, check the EXPLAIN ANALYZE output for the [HBO Modification] section. Its presence confirms HBO applied optimizations to the execution plan.

Example: hash table sizing

First, create a test table and temporarily lower the hash table size threshold to 1 so you can observe HBO without needing large row counts. Do not use this setting in production.

CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 SELECT i, i FROM generate_series(1, 1000000) i;
ANALYZE t1;

-- For testing only: lower the threshold to observe HBO behavior.
SET hg_adaptive_execution_hbo_generate_advice_hash_table_size_low_bound = 1;
EXPLAIN ANALYZE SELECT a, count(b) FROM t1 GROUP BY a;

Run the SELECT statement multiple times. After enough history is collected, the output will include an HBO Modification section:

QUERY PLAN
Gather  (cost=0.00..47.72 rows=1000000 width=12)
[21:1 id=100003 dop=1 time=1/1/1ms rows=1000000(1000000/1000000/1000000) mem=0/0/0B open=0/0/0ms get_next=1/1/1ms * ]
  ->  HashAggregate  (cost=0.00..18.70 rows=1000000 width=12)
        Group Key: a
      [id=4 dop=21 time=11/6/5ms rows=1000000(48052/47619/47219) mem=4/4/4MB open=11/6/5ms get_next=0/0/0ms rehash=1/1/1 hash_mem=3/3/3MB]
        ->  Redistribution  (cost=0.00..6.21 rows=1000000 width=8)
              Hash Key: a
            [21:21 id=100002 dop=21 time=1/0/0ms rows=1000000(48052/47619/47219) mem=0/0/0B open=0/0/0ms get_next=1/0/0ms * ]
              ->  Local Gather  (cost=0.00..5.02 rows=1000000 width=8)
                  [id=2 dop=21 time=0/0/0ms rows=1000000(49152/47619/40960) mem=0/0/0B open=0/0/0ms get_next=0/0/0ms local_dop=1/1/1 * ]
                    ->  Seq Scan on t1  (cost=0.00..5.01 rows=1000000 width=8)
                        [id=1 split_count=13 time=1/0/0ms rows=623168(49152/47936/40960) mem=576/576/576B open=1/0/0ms get_next=0/0/0ms scan_rows=623168(49152/47936/40960)]

ADVICE:
[node id : 100002] Distribution key mismatch. Table t1 is missing distribution keys. Requested distribution columns: a.

HBO Modification:
[node id: 4] Init Hash Table Size for Agg has been set to: 48052.

Query id:[101101885xxxx]
QE version: 2.0
Query Queue: default_queue
======================cost======================
Total cost:[141] ms
History Optimizer cost:[2.024] ms
Optimizer cost:[29] ms
Build execution plan cost:[1] ms
Init execution plan cost:[6] ms
Start query cost:[25] ms
- Queue cost: [0] ms
- Wait schema cost:[0] ms
- Lock query cost:[0] ms
- Create dataset reader cost:[0] ms
- Create split reader cost:[0] ms
Get result cost:[80] ms
- Get the first block cost:[8] ms
====================resource====================
Memory: total 110 MB. Worker stats: max 6 MB, avg 4 MB, min 2 MB, max memory worker id: 2385500434182507053.
CPU time: total 222 ms. Worker stats: max 18 ms, avg 9 ms, min 5 ms, max CPU time worker id: 2388922492294751946.
DAG CPU time stats: max 140 ms, avg 61 ms, min 0 ms, cnt 3, max CPU time dag id: 3.
Fragment CPU time stats: max 140 ms, avg 45 ms, min 0 ms, cnt 4, max CPU time fragment id: 3.
Ec wait time: total 34 ms. Worker stats: max 6 ms, max(max) 2 ms, avg 1 ms, min 0 ms, max ec wait time worker id: 2388922681335810605, max(max) ec wait time worker id: 2388922681335810605.
Read bytes: total 4 MB. Worker stats: max 0 MB, avg 0 MB, min 0 MB, max read bytes worker id: 2388922715412204922.
DAG instance count: total 43. Worker stats: max 3, avg 1, min 1, max DAG instance count worker id: 797329658507415939.
Fragment instance count: total 64. Worker stats: max 4, avg 2, min 1, max fragment instance count worker id: 797329658507415939.

What to look for:

`HBO Modification` section — Confirms HBO applied changes to this execution.
`rows=(48052/47619/47219)` in the HashAggregate node — Shows the estimated initial hash table size on each worker, derived from historical data.

View HBO storage usage

HBO historical statistics are stored in the instance and consume storage. To check how much:

SELECT PG_SIZE_PRETTY(hologres.hg_hbo_relation_size());

Example output:

pg_size_pretty
----
9940 kB

GUC parameter reference

Parameter	Scope	Description	Default
`hg_adaptive_execution_enable_history_based_optimization`	Session / DB	Enables or disables HBO.	`on`
`hg_adaptive_execution_enable_hbo_min_estimated_scan_rows`	Session / DB	Minimum estimated row count for a table before HBO collects statistics. Do not set below 100,000.	`100000`
`hg_adaptive_execution_hbo_generate_advice_hash_table_size_low_bound`	Session / DB	Minimum historical hash table row count required before HBO applies the hash table sizing optimization. Do not set below 100,000.	`100000`

FAQ

HBO is enabled, but `[HBO Modification]` does not appear in EXPLAIN ANALYZE. Why?

Check whether all prerequisites are met:

The query's estimated row count may be below the threshold (default: 100,000). Run EXPLAIN to check the estimated row count for each table in the plan.
The execution plan may not contain a Hash Agg node. HBO currently applies only to queries with Hash Agg operations.
The query may not have sufficient history. HBO requires the query to have run at least once within the last 48 hours with a total execution time exceeding 100 ms. Run the query a few more times to build up history, then check again.

Can I lower the row count threshold to get HBO to apply to smaller queries?

Technically yes, but avoid setting it below 100,000. For small hash tables, the performance benefit is minimal and the HBO framework overhead can reduce performance rather than improve it.