How Inceptor’s Cost‑Based Optimizer Boosts SQL Performance and How to Use It

Overview of CBO

Cost‑Based Optimization (CBO) is a cost‑driven optimizer for SQL execution plans, introduced in Hive 0.14 via Apache Calcite and enhanced in Inceptor. It evaluates alternative join orders and algebraic transformations, selecting the plan with the lowest estimated cost.

Using CBO

Before CBO can work, statistics must be collected. Inceptor provides two ways: the ANALYZE statement and the preanalyze.sh script. The script can generate and optionally execute ANALYZE commands for tables and columns based on the SQL to be optimized.

ANALYZE syntax

-- Collect table‑level statistics
ANALYZE TABLE <table_name> COMPUTE STATISTICS;

-- Collect column‑level statistics
ANALYZE TABLE <table_name> COMPUTE STATISTICS FOR COLUMNS [<col1>, <col2>, ...];

Preanalyze tool

The script is installed under /usr/lib/hive/bin/ and is invoked with options such as -f <file>, -d <directory>, -i <conf>, etc. Example commands:

./preanalyze.sh -f test.sql
./preanalyze.sh -d ./sqls -i my.conf
./preanalyze.sh -f getUsers.sql -i my.conf
./preanalyze.sh -d ./sqls --database mydb -a false

Enable CBO

Set the Hive parameter to turn on the optimizer:

SET hive.cbo.enable = TRUE;

Performance results

Benchmarks on TPC‑DS show Hive CBO improves average query time by 2.5×, while Inceptor CBO adds 3‑4× speedup for about 10 % of queries and up to 40 % for 20 % of queries, with 80 % of plans near optimal.

Conclusion

CBO is a practical optimizer for join‑heavy workloads in Inceptor, reducing manual tuning effort and accelerating large‑scale data warehouse queries.