An index selection method without repeated optimizer estimations

• Published in: Information sciences 2009-06, Vol.179 (13), p.2263-2272
• Main Authors: Kao, Kuo-Fong, Liao, I-En
• Format: Article
• Language: English
• Subjects: Configuration recommendation; Database management system (DBMS); Index selection problem (ISP); Optimizer; Performance tuning
• ISSN: 0020-0255; 1872-6291
• DOI: 10.1016/j.ins.2009.02.009

The index selection problem (ISP) concerns the selection of an appropriate index set to minimize the total cost for a given workload containing read and update queries. Since the ISP has been proven to be an NP-hard problem, most studies focus on heuristic algorithms to obtain approximate solutions. However, even approximate algorithms still consume a large amount of computing time and disk space because these systems must record all query statements and frequently request from the database optimizers the cost estimation of each query in each considered index. This study proposes a novel algorithm without repeated optimizer estimations. When a query is delivered to a database system, the optimizer evaluates the costs of various query plans and chooses an access path for the query. The information from the evaluation stage is aggregated and recorded with limited space. The proposed algorithm can recommend indexes according to the readily available information without querying the optimizer again. The proposed algorithm was tested in a PostgreSQL database system using TPC-H data. Experimental results show the effectiveness of the proposed approach.