On-line index selection for physical database tuning

Choosing an appropriate index configuration for a database, i.e., index selection, is an essential aspect of physical database design and a crucial step toward optimizing the performance of a database system. Index selection is also one of the more challenging tasks for database administrators. Hence, automated methods for index selection have been widely studied, and most modern database systems provide tools to help administrators choose a good index configuration for the workload. Most of the existing approaches to index selection have an off-line interface, meaning that they recommend an index configuration for a fixed workload. When the future workload is unknown or subject to change, off-line techniques are difficult to use. This observation suggests that an on-line interface to index selection would be valuable, in order to adapt the configuration for an evolving workload. This dissertation is focused on the problem of index selection in the on-line setting, where the workload may change over time. We make several contributions toward developing a robust solution to this problem. First, we conduct a thorough study of a major complicating factor in index selection, known in the literature as index interaction. We develop a novel formalism for reasoning about index interaction and illustrate the application of this framework for related physical design problems. We then present two new algorithms for on-line index selection. The first algorithm, COLT, is designed as a lightweight module that analyzes the workload with minimal overhead and materializes indexes based on a prediction of their future benefit. Our second algorithm, WFIT , performs a more exhaustive analysis based on rigorous techniques from on-line computation, while using knowledge of index interactions to make the analysis more efficient. Our next main contribution is a benchmark for on-line index selection algorithms, which we use to compare our algorithms as well as an existing technique for on-line index selection. The results of the benchmark show that COLT meets its goal of operating with very low overhead while selecting indexes with high benefit. We also observe that WFIT makes very robust decisions for the index configuration, which are superior in quality when compared to the state of the art. Finally, we introduce a novel semi-automatic algorithm for on-line index selection, which recommends indexes by continuously monitoring the workload. Unlike existing on-line approaches, the semi-automatic interface allows the database administrator to retain control over the physical design and provide feedback on the recommendations. This final contribution of the dissertation promises to make on-line index selection more user-friendly in practice.