Automated physical designers: what you see is (not) what you get

  • Authors:

  • Renata Borovica;Ioannis Alagiannis;Anastasia Ailamaki

  • Affiliations:

  • École Polytechnique Fédérale de Lausanne, Lausanne, Switzerland;École Polytechnique Fédérale de Lausanne, Lausanne, Switzerland;École Polytechnique Fédérale de Lausanne, Lausanne, Switzerland

  • Venue:
  • DBTest '12 Proceedings of the Fifth International Workshop on Testing Database Systems
  • Year:
  • 2012

Quantified Score

Hi-index 0.00

Visualization

Abstract

The explosion of available data in the last few years has increased the importance of physical database design, since the selection of proper physical structures (e.g. indices, partitions and materialized views) may improve query execution performance by several orders of magnitude. Commercial DBMS vendors have recognized this need and offered automated physical design tools as part of their products. These tools use what-if interfaces to simulate the presence of different physical structures and recommend physical designs that minimize the estimated execution time of a given workload. Along with the recommended design, they deliver an estimation of the expected improvement the new design brings. In this paper, we examine the output of physical designers, i. e., whether what we see as a result of the tuning (the estimation of the improvement) is indeed what we may expect after applying the design (the actual improvement). We evaluate three commercial physical designers by varying their input parameters on real and synthetic data sets. Our results show that all three physical designers exhibit highly unpredictable behavior in certain cases, indicating that there is still significant room for improvement in terms of their predictability and consequently, their quality.