MFIX: An Efficient and Reliable Index Advisor via Multi-Fidelity Bayesian Optimization

Indexes play a pivotal role in enhancing database performance. However, index selection remains one of the most challenging problems in relational database management systems, as it demands a careful equilibrium: the search procedure needs to efficiently navigate through a multitude of potential configu-rations, while the evaluation method needs to precisely assess the performance impact of index configurations. Specifically, prohibitively high costs can arise from frequent index creation and workload execution for evaluation, whereas over-reliance on cost estimations can yield suboptimal performance due to potential inaccuracies. In this paper, we present a multi-fidelity index advisor, MFIX, designed to reconcile search efficiency and solution quality. To balance evaluation accuracy and efficiency, MFIX coordinates a range of low-fidelity cost estimates as cheap-to-evaluate ap-proximations, with a select few precise high-fidelity workload executions for refinement. To optimize search efficiency, MFIX employs a data-efficient Bayesian optimization method, paired with a condensed tree-structured index space that eliminates redundant configurations. Furthermore, MFIX incorporates his-torical tasks as auxiliary information with variable fidelity, using an adaptive weighting mechanism that considers task similarity to expedite the search process. Extensive experiments with diverse analytical workloads show that MFIX consistently out-performs state-of-the-art single-fidelity methods, achieving up to a 10.2% increase in performance improvement in actual execution cost over the leading estimation-based approach. Furthermore, through its multi-fidelity Bayesian optimization over conditional space, MFIX significantly enhances the search efficiency and ensures a sustainable search cost.