Autonomous Management of Soft Indexes

Abstract

In recent years the support for index tuning as part of physical database design has gained focus in research and product development, which resulted in index and design advisors. Nevertheless, these tools provide a one-off solution for a continuous task and are not deeply integrated with the DBMS functionality by only applying the query optimizer for index recommendation and profit estimation and decoupling the decision about and execution of index configuration changes from the core system functionality. In this paper we propose an approach that continuously collects statistics for recommended indexes and based on this, repetitively solves the Index Selection Problem (ISP). A key novelty is the on-the-fly index generation during query processing implemented by new query plan operators IndexBuildScan and SwitchPlan. Finally, we present the implementation and evaluation of the introduced concepts as part of the PostgreSQL system.

Benchmarking Adaptive Indexing

“…More recently online index creation approaches have been introduced [2,12,13]. They extend the above model for the cases where the workload is not known up-front.…”

“…As the authors of two different approaches to adaptive indexing [6,7,9,10,11] and long-time admirers of previous self-tuning approaches [1,2,3,12,13] we propose here a framework for benchmarking adaptive indexing systems. One design goal is that the framework should be able to measure the incremental costs and benefits of reorganization actions in terms of how these are distributed along the lifetime of a workload.…”

“…A third approach is based on online tuning and loads data quickly without indexes. It first observes the workload and then identifies and constructs the most promising indexes [2,12,13]. Unlike all of these methods, adaptive indexing (bottom row) creates and refines indexes automatically and incrementally, as a side effect of query execution [6,7,9,10,11].…”

Transactional support for adaptive indexing

“…"Soft indexes" automatically and autonomously manage indexes in response to a workload [32]. Like the monitor-andtune approaches, this approach continually collects statistics for recommended indexes and periodically and repeatedly automatically solves the index selection problem.…”

“…Like the monitor-andtune approaches, this approach continually collects statistics for recommended indexes and periodically and repeatedly automatically solves the index selection problem. Unlike typical monitor-and-tune approaches and like adaptive indexing approaches, [32] then generates (or drops) the recommended indexes as a part of query processing. Unlike adaptive indexing approaches such as database cracking and adaptive merging, however, neither index recommendation nor creation is incremental; explicit statistics are kept, and each recommended index is created and optimized to completion (although the command might be deferred).…”