Wii: Dynamic Budget Reallocation In Index Tuning

TL;DR

Wii addresses the high cost of budgeted index tuning by intercepting and bounding what-if optimizer calls. It establishes a mathematically grounded framework with a computable lower bound on what-if cost and upper bounds on marginal cost improvements, enabling confident skipping of certain QCP evaluations. When integrated with existing budget-aware search algorithms (e.g., two-phase greedy or MCTS), Wii substantially reduces unnecessary calls and reallocates budget to informative evaluations, yielding better final configurations with only modest overhead. The approach demonstrates strong end-to-end improvements across industrial benchmarks and real workloads, and remains effective even under storage constraints or unlimited-budget scenarios, illustrating practical impact for scalable index tuning.

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.

Figures (22)

• Figure 1: The architecture of budget-aware index tuning with "Wii", i.e., what-if (call) interception, where $W$ represents the input workload, $q_i\in W$ represents an individual SQL query in the workload, $\Gamma$ represents a set of tuning constraints, $B$ represents the budget on the number of what-if calls allowed. Moreover, $\{z_j\}$ represents the set of candidate indexes generated for $W$, and $C\subseteq\{z_j\}$ represents an index configuration proposed during configuration enumeration.
• Figure 2: Distribution of the relative gap between what-if cost and derived cost when tuning TPC-DS under a budget of 5,000 what-if calls. Here the relative gap is defined as $\frac{\text{derived cost}-\text{what-if cost}}{\text{derived cost}}\times 100\%$, as derived cost is an upper bound of the what-if cost under monotonicity assumption.
• Figure 3: Example of budget-aware greedy search.
• Figure 4: Example of budget allocation in MCTS.
• Figure 5: The definition and estimation of "coverage."

Theorems & Definitions (8)

• Definition 1: Derived Cost
• Definition 2: Marginal Cost Improvement
• Definition 3: Cost Improvement
• Definition 4: Naive Upper Bound
• Lemma 1
• Definition 5: Submodular Upper Bound
• Theorem 1
• Theorem 2