Delta: A Learned Mixed Cost-based Query Optimization Framework

TL;DR

Delta is a novel query optimization framework that combines a query compatibility detector with a two-stage planner, leveraging learned models to generate higher-quality plans more efficiently than existing methods.

Contribution

It introduces a mixed cost-based optimization approach with a compatibility detector and a two-stage planner, reducing training costs and improving plan quality.

Findings

Achieves 2.34x speedup over PostgreSQL.

Outperforms state-of-the-art learned methods by 2.21x.

Effectively filters incompatible queries to improve efficiency.

Abstract

Query optimizer is a crucial module for database management systems. Existing optimizers exhibit two flawed paradigms: (1) cost-based optimizers use dynamic programming with cost models but face search space explosion and heuristic pruning constraints; (2) value-based ones train value networks to enable efficient beam search, but incur higher training costs and lower accuracy. They also lack mechanisms to detect queries where they may perform poorly. To determine more efficient plans, we propose Delta, a mixed cost-based query optimization framework that consists of a compatible query detector and a two-stage planner. Delta first employs a Mahalanobis distancebased detector to preemptively filter out incompatible queries where the planner might perform poorly. For compatible queries, Delta activates its two-stage mixed cost-based planner. Stage I serves as a coarse-grained filter to generate high-quality candidate plans based on the value network via beam search, relaxing precision requirements and narrowing the search space. Stage II employs a fine-grained ranker to determine the best plan from the candidate plans based on a learned cost model. Moreover, to reduce training costs, we reuse and augment the training data from stage I to train the model in stage II. Experimental results on three workloads demonstrate that Delta identifies higher-quality plans, achieving an average 2.34x speedup over PostgreSQL and outperforming the state-of-the-art learned methods by 2.21x.