English

HERO: Hint-Based Efficient and Reliable Query Optimizer

Databases 2024-12-06 v2 Artificial Intelligence Machine Learning

Abstract

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.

Keywords

Cite

@article{arxiv.2412.02372,
  title  = {HERO: Hint-Based Efficient and Reliable Query Optimizer},
  author = {Sergey Zinchenko and Sergey Iazov},
  journal= {arXiv preprint arXiv:2412.02372},
  year   = {2024}
}

Comments

Submitted to VLDB 2025; 13 pages; 13 figures

R2 v1 2026-06-28T20:21:13.795Z