arXivDaily arXiv每日学术速递 周一至周五更新
arXiv 2411.04525cs.DBcs.AI
收藏

GenJoin:从子计划提示中学习的条件生成式计划到计划查询优化器

GenJoin: Conditional Generative Plan-to-Plan Query Optimizer that Learns from Subplan Hints

Zurich University of Applied Sciences(应用科学大学苏黎世)

Pavel Sulimov, Claude Lehmann, Kurt Stockinger

AI总结:

本研究提出新型生成式学习查询优化器GenJoin,将查询优化视为生成任务,从子计划提示中学习生成执行计划,在真实基准测试中显著优于PostgreSQL和现有最优方法,解决了传统学习型优化器数据需求大、推理慢、结果不稳定的问题。

摘要:

查询优化领域的经典算法正受到机器学习算法的挑战。同时,近期学习型查询优化器的研究趋势表明,充分利用数十年的数据库研究成果,通过不同类型的提示(例如指定连接类型、扫描类型或连接顺序)缩小计划搜索空间,以此增强经典查询优化器,而非用机器学习模型完全取代经典查询优化器,是更为审慎的做法。这一点在经典优化器无法完整枚举所有逻辑和物理计划,转而需要依赖遗传算法等鲁棒性较弱的方法的场景中尤为重要。然而,即便是协同式学习查询优化器,也受限于训练数据需求量大、推理阶段计划生成速度慢,以及在不同工作负载条件下结果不稳定等问题。本文提出了GenJoin——一种新型学习型查询优化器,它将查询优化问题视为生成式任务,能够从随机的子计划提示集合中学习,生成性能优于经典优化器的查询计划。在两个知名的真实基准测试集上,针对多种工作负载开展严格的机器学习评估后表明,GenJoin是首个能够显著且持续优于PostgreSQL以及现有最优方法的学习型查询优化器。

英文摘要:

Query optimization has become a research area where classical algorithms are being challenged by machine learning algorithms. At the same time, recent trends in learned query optimizers have shown that it is prudent to take advantage of decades of database research and augment classical query optimizers by shrinking the plan search space through different types of hints (e.g. by specifying the join type, scan type or the order of joins) rather than completely replacing the classical query optimizer with machine learning models. It is especially relevant for cases when classical optimizers cannot fully enumerate all logical and physical plans and, as an alternative, need to rely on less robust approaches like genetic algorithms. However, even symbiotically learned query optimizers are hampered by the need for vast amounts of training data, slow plan generation during inference and unstable results across various workload conditions. In this paper, we present GenJoin - a novel learned query optimizer that considers the query optimization problem as a generative task and is capable of learning from a random set of subplan hints to produce query plans that outperform the classical optimizer. GenJoin is the first learned query optimizer that significantly and consistently outperforms PostgreSQL as well as state-of-the-art methods on two well-known real-world benchmarks across a variety of workloads using rigorous machine learning evaluations.

机构、总结、中文摘要均由AI分析而来,可能存在错误,请以论文正文为准。

↑