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 the native query optimizer and continuously learns to improve query optimization. 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 effectiveness. Rather than building a learned optimizer from scratch, Lero is designed to leverage decades of wisdom of databases and improve the native optimizer. With its non intrusive design, Lero can be implemented on top of any existing DBMS with minimum 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 execution time of the native PostgreSQL optimizer 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.

翻译：近期一系列工作尝试应用机器学习技术辅助或重建数据库管理系统中的基于代价的查询优化器。尽管这些方法在某些基准测试中表现出优越性，但其存在的缺陷——例如性能不稳定、训练成本高以及模型更新缓慢——均源于使用机器学习模型预测执行计划代价或延迟这一任务的固有限度。本文提出一种名为Lero的基于学习排序的查询优化器，该优化器构建于原生查询优化器之上，并通过持续学习改进查询优化过程。关键发现是：对于查询优化而言，计划之间的相对顺序或排序信息足以替代精确的代价或延迟数值。Lero采用成对方法训练分类器，使其能够比较任意两个计划并判断孰优孰劣。相较于预测代价或延迟的回归任务，这种二分类任务在模型效率与有效性方面均更具优势。Lero并非从零构建学习型优化器，而是旨在利用数据库领域数十年积淀的智慧来增强原生优化器。其非侵入式设计使Lero能够以最小集成代价部署于任何现有数据库管理系统之上。我们实现了Lero，并在PostgreSQL上验证了其卓越性能。实验表明，Lero在多个基准测试中接近最优性能。与PostgreSQL原生优化器相比，其执行时间最多降低70%；相较于其他学习型查询优化器，执行时间最多降低37%。同时，Lero能够持续学习并自动适应查询负载与数据变化。