# Lero: A Learning-to-Rank Query Optimizer

arXiv: https://arxiv.org/abs/2302.06873 · HTML: https://mlsanity.com/paper/2302.06873/

## Key facts

- Cited by 26 papers in the map (data through 14 Oct 2026).
- Builds on 20 of its 57 references that are in the map.
- First posted on arXiv 14 Feb 2023.

## Builds on (most cited first, 20 of 20)

- [Revisiting Distributed Synchronous SGD](https://mlsanity.com/paper/1702.05800/) (2017)
- [Neo: A Learned Query Optimizer](https://mlsanity.com/paper/1904.03711/) (2019)
- [Learned Cardinalities: Estimating Correlated Joins with Deep Learning](https://mlsanity.com/paper/1809.00677/) (2018)
- [Bao: Learning to Steer Query Optimizers](https://mlsanity.com/paper/2004.03814/) (2020)
- [SetRank: Learning a Permutation-Invariant Ranking Model for Information Retrieval](https://mlsanity.com/paper/1912.05891/) (2019)
- [An End-to-End Learning-based Cost Estimator](https://mlsanity.com/paper/1906.02560/) (2019)
- [Deep Unsupervised Cardinality Estimation](https://mlsanity.com/paper/1905.04278/) (2019)
- [A Survey on Contextual Multi-armed Bandits](https://mlsanity.com/paper/1508.03326/) (2015)
- [Learning to Optimize Join Queries With Deep Reinforcement Learning](https://mlsanity.com/paper/1808.03196/) (2018)
- [Cardinality Estimation in DBMS: A Comprehensive Benchmark Evaluation](https://mlsanity.com/paper/2109.05877/) (2021)
- [PiRank: Scalable Learning To Rank via Differentiable Sorting](https://mlsanity.com/paper/2012.06731/) (2020)
- [Plan-Structured Deep Neural Network Models for Query Performance Prediction](https://mlsanity.com/paper/1902.00132/) (2019)
- [FLAT: Fast, Lightweight and Accurate Method for Cardinality Estimation](https://mlsanity.com/paper/2011.09022/) (2020)
- [NeuroCard: One Cardinality Estimator for All Tables](https://mlsanity.com/paper/2006.08109/) (2020)
- [DeepDB: Learn from Data, not from Queries!](https://mlsanity.com/paper/1909.00607/) (2019)
- [Are We Ready For Learned Cardinality Estimation?](https://mlsanity.com/paper/2012.06743/) (2020)
- [Balsa: Learning a Query Optimizer Without Expert Demonstrations](https://mlsanity.com/paper/2201.01441/) (2022)
- [SkinnerDB: Regret-Bounded Query Evaluation via Reinforcement Learning](https://mlsanity.com/paper/1901.05152/) (2019)
- [BayesCard: Revitilizing Bayesian Frameworks for Cardinality Estimation](https://mlsanity.com/paper/2012.14743/) (2020)
- [Efficient Deep Learning Pipelines for Accurate Cost Estimations Over Large Scale Query Workload](https://mlsanity.com/paper/2103.12465/) (2021)

## Built on by (most cited first, 26 of 26)

- [E2ETune: End-to-End Knob Tuning via Fine-tuned Generative Language Model](https://mlsanity.com/paper/2404.11581/) (2024)
- [CARROT: A Learned Cost-Constrained Retrieval Optimization System for RAG](https://mlsanity.com/paper/2411.00744/) (2024)
- [NeurDB: An AI-powered Autonomous Data System](https://mlsanity.com/paper/2405.03924/) (2024)
- [NeurDB: On the Design and Implementation of an AI-powered Autonomous Database](https://mlsanity.com/paper/2408.03013/) (2024)
- [RCRank: Multimodal Ranking of Root Causes of Slow Queries in Cloud Database Systems](https://mlsanity.com/paper/2503.04252/) (2025)
- [A Learned Cost Model-based Cross-engine Optimizer for SQL Workloads](https://mlsanity.com/paper/2506.02802/) (2025)
- [AI-Driven Research for Databases](https://mlsanity.com/paper/2604.06566/) (2026)
- [FOSS: A Self-Learned Doctor for Query Optimizer](https://mlsanity.com/paper/2312.06357/) (2023)
- [Robust Plan Evaluation based on Approximate Probabilistic Machine Learning](https://mlsanity.com/paper/2401.15210/) (2024)
- [GenJoin: Conditional Generative Plan-to-Plan Query Optimizer that Learns from Subplan Hints](https://mlsanity.com/paper/2411.04525/) (2024)
- [Query Performance Explanation through Large Language Model for HTAP Systems](https://mlsanity.com/paper/2412.01709/) (2024)
- [Large Scale Multi-Task Bayesian Optimization with Large Language Models](https://mlsanity.com/paper/2503.08131/) (2025)
- [Reqo: A Comprehensive Learning-Based Cost Model for Robust and Explainable Query Optimization](https://mlsanity.com/paper/2501.17414/) (2025)
- [Low Rank Learning for Offline Query Optimization](https://mlsanity.com/paper/2504.06399/) (2025)
- [Training-Free Query Optimization via LLM-Based Plan Similarity](https://mlsanity.com/paper/2506.05853/) (2025)
- [LLM4Hint: Leveraging Large Language Models for Hint Recommendation in Offline Query Optimization](https://mlsanity.com/paper/2507.03384/) (2025)
- [AutoIndexer: A Reinforcement Learning-Enhanced Index Advisor Towards Scaling Workloads](https://mlsanity.com/paper/2507.23084/) (2025)
- [Bootstrapping Learned Cost Models with Synthetic SQL Queries](https://mlsanity.com/paper/2508.19807/) (2025)
- [OntoTune: Ontology-Driven Learning for Query Optimization with Convolutional Models](https://mlsanity.com/paper/2511.06780/) (2025)
- [CoLSE: A Lightweight and Robust Hybrid Learned Model for Single-Table Cardinality Estimation using Joint CDF](https://mlsanity.com/paper/2512.12624/) (2025)
- [Piece of CAKE: Adaptive Execution Engines via Microsecond-Scale Learning](https://mlsanity.com/paper/2602.04181/) (2026)
- [MICRO: A Lightweight Middleware for Optimizing Cross-store Cross-model Graph-Relation Joins [Technical Report]](https://mlsanity.com/paper/2603.13835/) (2026)
- [RELOAD: A Robust and Efficient Learned Query Optimizer for Database Systems](https://mlsanity.com/paper/2604.14725/) (2026)
- [Opti-Q: A Constraint-Based Optimization Framework for Multi-LLM Question Planning](https://mlsanity.com/paper/2607.22621/) (2026)
- [Fully Inductive Cardinality Estimation](https://mlsanity.com/paper/2607.28311/) (2026)
- [ScaleSense: Cost-Intelligent Scaling Framework via Learned Resource Estimation in Alibaba AnalyticDB](https://mlsanity.com/paper/2608.07945/) (2026)

## Abstract (arXiv)

A recent line of works apply machine learning techniques to assist or rebuild cost-based query optimizers in DBMS. While exhibiting superiority in some benchmarks, their deficiencies, e.g., unstable performance, high training cost, and slow model updating, stem from the inherent hardness of predicting the cost or latency of execution plans using machine learning models. In this paper, we introduce a learning-to-rank query optimizer, called Lero, which builds on top of a native query optimizer and continuously learns to improve the optimization performance. The key observation is that the relative order or rank of plans, rather than the exact cost or latency, is sufficient for query optimization. Lero employs a pairwise approach to train a classifier to compare any two plans and tell which one is better. Such a binary classification task is much easier than the regression task to predict the cost or latency, in terms of model efficiency and accuracy. Rather than building a learned optimizer from scratch, Lero is designed to leverage decades of wisdom of databases and improve the native query optimizer. With its non-intrusive design, Lero can be implemented on top of any existing DBMS with minimal integration efforts. We implement Lero and demonstrate its outstanding performance using PostgreSQL. In our experiments, Lero achieves near optimal performance on several benchmarks. It reduces the plan execution time of the native optimizer in PostgreSQL by up to 70% and other learned query optimizers by up to 37%. Meanwhile, Lero continuously learns and automatically adapts to query workloads and changes in data.