AI Meets AI: Leveraging Query Executions to Improve Index Recommendations

State-of-the-art index tuners rely on query optimizer’s cost estimates to search for the index configuration with the largest estimated execution cost improvement. Due to well-known limitations in optimizer’s estimates, in a significant fraction of cases, an index estimated to improve a query’s execution cost, e.g., CPU time, makes that worse when implemented. Such errors are a major impediment for automated indexing in production systems.

We observe that comparing the execution cost of two plans of the same query corresponding to different index configurations is a key step during index tuning. Instead of using optimizer’s estimates for such comparison, our key insight is that formulating it as a classification task in machine learning results in significantly higher accuracy. We present a study of the design space for this classification problem. We further show how to integrate this classifier into the state-of-the-art index tuners with minimal modifications, i.e., how artificial intelligence (AI) can benefit automated indexing (AI). Our evaluation using industry-standard benchmarks and a large number of real customer workloads demonstrates up to 5x reduction in the errors in identifying the cheaper plan in a pair, which eliminates almost all query execution cost regressions when the model is used in index tuning.