A Tool for Automatic Index Selection in Database Management Systems

A task commonly undertaken by database administrators to speed up the performance of commands submitted to a DBMS is the selection of indexes for the tables. This paper introduces the concept of how to use the Database Management System (DBMS) Optimizer to select their own indexes. We introduce a heuristic to guide the index selection process, which is implemented by a tool called AISIO that runs integrated to the DBMS. Our tool captures Structured Query Language (SQL) statements submitted to the DBMS, analyzes them and, based on the implemented heuristic and on the DBMS Optimizer statistics, recommends which indexes could potentially optimize the performance of those transactions. The tool also provides a measure of the performance gains obtained from the use of the recommended indexes. To illustrate our tool, we integrate it to the PostgreSQL DBMS and experimental results are presented to evidence our contributions.