We propose a novel model for learned query optimization which provides query hints leading to better execution plans. The model addresses the three key challenges in learned hint-based query optimization: reliable hint recommendation (ensuring non-degradation of query latency), efficient hint exploration, and fast inference. We provide an in-depth analysis of existing NN-based approaches to hint-based optimization and experimentally confirm the named challenges for them. Our alternative solution consists of a new inference schema based on an ensemble of context-aware models and a graph storage for reliable hint suggestion and fast inference, and a budget-controlled training procedure with a local search algorithm that solves the issue of exponential search space exploration. In experiments on standard benchmarks, our model demonstrates optimization capability close to the best achievable with coarse-grained hints. Controlling the degree of parallelism (query dop) in addition to operator-related hints enables our model to achieve 3x latency improvement on JOB benchmark which sets a new standard for optimization. Our model is interpretable and easy to debug, which is particularly important for deployment in production.

翻译：我们提出了一种新颖的查询优化学习模型，该模型通过提供查询提示来生成更优的执行计划。该模型解决了基于提示的学习型查询优化中的三个关键挑战：可靠的提示推荐（确保查询延迟不退化）、高效的提示探索以及快速推理。我们对现有基于神经网络的提示优化方法进行了深入分析，并通过实验验证了这些方法所面临的上述挑战。我们的替代解决方案包含：一个基于上下文感知模型集成的新型推理架构，以及用于可靠提示建议和快速推理的图存储系统；同时采用预算控制的训练流程配合局部搜索算法，以解决指数级搜索空间探索问题。在标准基准测试实验中，我们的模型展现出接近粗粒度提示所能达到的最佳优化能力。通过同时控制并行度（查询dop）和运算符相关提示，我们的模型在JOB基准测试中实现了3倍的延迟提升，为查询优化树立了新标准。该模型具有可解释性和易调试性，这对实际生产部署尤为重要。