Autonomous Management of Soft Indexes

In recent years the support for index tuning as pan 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 (lSP).A key novelty is the on-the-fly index generation during query processing implemented by new query plan operators In-dexBuildScan and SwitchPlan. Finally, we present the implementation and evaluation of the introduced concepts as part of the PostgreSQL system.

[1]  Daniel C. Zilio,et al.  DB2 advisor: an optimizer smart enough to recommend its own indexes , 2000, Proceedings of 16th International Conference on Data Engineering (Cat. No.00CB37073).

[2]  Betty Salzberg,et al.  Bulletin of the Technical Committee on Data Engineering , 1995 .

[3]  Jeffrey O. Kephart,et al.  The Vision of Autonomic Computing , 2003, Computer.

[4]  Kai-Uwe Sattler,et al.  Autonomous query-driven index mining , 2004, Proceedings. International Database Engineering and Applications Symposium, 2004. IDEAS '04..

[5]  Goetz Graefe,et al.  Optimization of dynamic query evaluation plans , 1994, SIGMOD '94.

[6]  Kai-Uwe Sattler,et al.  QUIET: Continuous Query-driven Index Tuning , 2003, VLDB.

[7]  Serge Abiteboul,et al.  COLT: continuous on-line tuning , 2006, SIGMOD Conference.

[8]  Douglas Comer,et al.  The difficulty of optimum index selection , 1978, TODS.

[9]  Matteo Fischetti,et al.  Exact and Approximate Algorithms for the Index Selection Problem in Physical Database Design , 1995, IEEE Trans. Knowl. Data Eng..

[10]  Surajit Chaudhuri,et al.  Database tuning advisor for microsoft SQL server 2005: demo , 2005, SIGMOD '05.

[11]  Vivek R. Narasayya,et al.  Automatic physical design tuning: workload as a sequence , 2006, SIGMOD Conference.

[12]  Gerhard Weikum,et al.  The COMFORT Automatic Tuning Project, Invited Project Review , 1994, Inf. Syst..

[13]  Karen Ward,et al.  Dynamic query evaluation plans , 1989, SIGMOD '89.

[14]  Sam Lightstone,et al.  DB2 Design Advisor: Integrated Automatic Physical Database Design , 2004, VLDB.

[15]  Surajit Chaudhuri,et al.  Database Tuning Advisor for Microsoft SQL Server 2005 , 2004, VLDB.

[16]  Surajit Chaudhuri,et al.  To tune or not to tune?: a lightweight physical design alerter , 2006, VLDB.

[17]  Surajit Chaudhuri,et al.  An Online Approach to Physical Design Tuning , 2007, 2007 IEEE 23rd International Conference on Data Engineering.

[18]  Deeparnab Chakrabarty,et al.  Knapsack Problems , 2008 .

[19]  Surajit Chaudhuri,et al.  Index selection for databases: a hardness study and a principled heuristic solution , 2004, IEEE Transactions on Knowledge and Data Engineering.