A cost-aware and workload-based index advisor for columnar in-memory databases

Abstract

Optimal index configurations for in-memory databases differ significantly from configurations for their traditional disk-based counterparts. Operations like full column scans that have previously been prohibitively expensive in disk-based and row-oriented databases are now computationally feasible with columnar main memory-resident data structures and even outperform index-based accesses in many cases. Furthermore, index selection criteria are different for in-memory databases since maintenance costs are often lower while memory footprint considerations have become increasingly important. In this paper, we introduce a workload-based and cost-aware index advisor tailored for columnar in-memory databases in mixed workload environments. We apply a memory traffic-driven model to estimate the efficiency of each index and to give a system-wide overview of the indices that are cost-ineffective with respect to their size and performance improvement. We also present our Index Advisor Cockpit applied to a real-world live production enterprise system of a Global 2000 company.