Reproduce “Lero: A Learning-to-Rank Query Optimizer” on OpenResearch

About this paper

Rong Zhu, Wei Chen, Bolin Ding, Xingguang Chen, Andreas Pfadler, Ziniu Wu, Jingren Zhou

A query optimizer from Alibaba Group, Lero, applies a learning-to-rank paradigm to select optimal execution plans by training a model to predict the relative order of plan efficiencies rather than their absolute costs. This approach enhances native optimizers, reducing query execution time by up to 70% on benchmarks like IMDB while mitigating performance regressions.

Key insights

• Learning the *relative order* of query plan efficiencies is a more robust and achievable task for ML models than predicting absolute execution latencies, which is sufficient for effective optimization.
• Pre-training the learned comparator model with a native optimizer's estimated costs provides a strong initial baseline, accelerating convergence and ensuring immediate performance without a 'cold-start' period.
• Manipulating native cardinality estimators offers an effective and non-intrusive strategy for the plan explorer to generate diverse, high-quality candidate plans for the learning model.
• A 1-dimensional embedding space for plan features proved more effective than higher dimensions for the learning-to-rank task, indicating that simplicity in representation can be beneficial.

Summary by alphaXiv.