AUTONOMOUS INDEX RECOMMENDATION SYSTEM FOR RELATIONAL DATABASES BASED ON COST-MODEL AND PLAN COVERING

Background. Indexing is a key tool for improving the performance of relational databases, but manual index selection remains complex and subjective. As data volumes grow, the demands on the adaptability of optimization systems increase. Machine and deep learning-based approaches provide high accuracy, but require significant resources and have a “cold start” problem, which necessitates autonomous solutions with balanced accuracy and complexity characteristics.

Objective. Development and experimental evaluation of an autonomous index recommendation system that combines a cost-model with a Plan Covering algorithm to improve query performance in relational databases while simultaneously taking into account the cost of index storage.

Methods. The system has a pipeline architecture and includes a workload analyzer, a cost model, and a Plan Covering algorithm. The analyzer generates a query profile, the cost model evaluates their performance with hypothetical indexes, and the algorithm performs a greedy index selection based on the benefit-to-cost ratio. Experimental testing was performed on a synthetic e-commerce system dataset in the PostgreSQL environment.

Results. The system generated 7 index recommendations, of which 6 were successfully created. The average query execution time improvement was 22.54%, with the largest improvement (26.06%) being obtained for the most frequently executed query with the JOIN operation. The total execution time of all queries decreased from 0.2751 s to 0.2131 s.

Conclusions. The proposed combination of Cost-Model and Plan Covering provides effective automation of the indexing process without the use of resource-intensive neural network approaches, demonstrating a significant improvement in performance and rational use of storage resources.