DISTILL: Low-Overhead Data-Driven Techniques for Filtering and Costing Indexes for Scalable Index Tuning

Download PDF

Abstract

Many database systems offer index tuning tools that help automatically select appropriate indexes for improving the performance of an input workload. However, index tuning is a resource-intensive task requiring expensive optimizer calls for estimating the cost of queries over candidate index configurations. In this work, we develop low-overhead techniques that can be leveraged by index tuning tools for reducing a large number of optimizer calls without making any changes to the tuning algorithm or to the query optimizer. First, index tuning tools use rule-based techniques to generate a large number of syntactically-relevant indexes; however, a large proportion of such indexes are spurious and do not lead to a significant improvement in the performance of queries. We eliminate such indexes much earlier in the search by leveraging patterns and statistics in the workload, without making optimizer calls. Second, we further reduce optimizer calls using index-specific cost models that exploit the commonality across optimizer calls between similar query and configuration pairs in the workload. We perform an extensive evaluation over both real-world and synthetic benchmarks, and show that our proposed techniques can reduce the tuning time in a range of 2X to 12X with a median reduction of 5X compared to state-of-the-art tuning tools without significantly degrading the quality of recommended indexes.