Breaking It Down: An In-Depth Study of Index Advisors

Index advisors aim to improve workload performance by judiciously selecting an appropriate set of indexes. Various heuristic-based and learning-based methods have been proposed. However, there lacks a comprehensive assessment of existing index advisors, i.e., their advantages, limitations, and application scenarios. In this work, we conduct an in-depth study of existing index advisors in five key aspects. First, we initiate an end-to-end analysis, i.e., a completed analysis throughout the entire workflow of index advisors. We decompose index advisors into three essential building blocks, establish a taxonomy to classify methods used in each block, and analyze the strengths and weaknesses of these methods. Second, we develop a unified open-source testbed, implementing seventeen index advisors across eleven open-source or real-world datasets. We enable customizable configurations to meet diverse testing requirements. Third, we conduct an extensive assessment of index advisors across database systems in various scenarios. We evaluate their adaptability and robustness, identifying practical application scenarios. Fourth, we undertake a fine-grained ablation study by investigating variants of each building block. We identify effective variants and pinpoint significant factors impacting index advisors' performance via explainable machine-learning techniques. Lastly, we consolidate our findings that could shed light on research directions to advance the future development of index advisors.