Database Tuning with Partial Indexes
Alain D. Fuentes, Ana Carolina Almeida, Rogério Luís de C. Costa, Vanessa P. Braganholo, Sérgio Lifschitz · 2018
Database tuning usually involves indexes, materialized views, partitioning, query rewriting and other techniques. One strategy that presents good results for performance improvements is the use of partial indexes. However, partial indexes have not been used for database tuning in the past. This is because the search space for partial indexes is exponential in the number of attributes and tuples of the table. In this paper, we address this problem by proposing an optimized strategy to select partial indexes. The optimization relies on reducing the amount of logic reads. We explain how to select the indexable attributes and their corresponding restrictions through a formal procedure. We implement our strategy to illustrate the benefits of partial indexes for tuning issues. Results are promising.