Wii: Dynamic Budget Reallocation In Index Tuning

TL;DR

Wii is a lightweight mechanism that improves index tuning efficiency by intelligently reallocating budget to avoid unnecessary what-if calls, leading to better configurations and reduced resource waste.

Contribution

It introduces Wii, a novel method for dynamic budget reallocation that enhances existing index tuning algorithms by reducing spurious calls and optimizing resource use.

Findings

Wii significantly reduces unnecessary what-if calls.

Reallocating saved budget improves final index configurations.

Experimental results show Wii's effectiveness on benchmarks and real workloads.

Abstract

Index tuning aims to find the optimal index configuration for an input workload. It is often a time-consuming and resource-intensive process, largely attributed to the huge amount of "what-if" calls made to the query optimizer during configuration enumeration. Therefore, in practice it is desirable to set a budget constraint that limits the number of what-if calls allowed. This yields a new problem of budget allocation, namely, deciding on which query-configuration pairs (QCPs) to issue what-if calls. Unfortunately, optimal budget allocation is NP-hard, and budget allocation decisions made by existing solutions can be inferior. In particular, many of the what-if calls allocated by using existing solutions are devoted to QCPs whose what-if costs can be approximated by using cost derivation, a well-known technique that is computationally much more efficient and has been adopted by commercial index tuning software. This results in considerable waste of the budget, as these what-if calls are unnecessary. In this paper, we propose "Wii," a lightweight mechanism that aims to avoid such spurious what-if calls. It can be seamlessly integrated with existing configuration enumeration algorithms. Experimental evaluation on top of both standard industrial benchmarks and real workloads demonstrates that Wii can eliminate significant number of spurious what-if calls. Moreover, by reallocating the saved budget to QCPs where cost derivation is less accurate, existing algorithms can be significantly improved in terms of the final configuration found.