Preventing bad plans by bounding the impact of cardinality estimation errors

  • Authors:

  • Guido Moerkotte;Thomas Neumann;Gabriele Steidl

  • Affiliations:

  • University of Mannheim, Mannheim, Germany;Max Planck Institute for Informatics, Saarbrücken, Germany;University of Mannheim, Mannheim, Germany

  • Venue:
  • Proceedings of the VLDB Endowment
  • Year:
  • 2009

Quantified Score

Hi-index 0.00

Visualization

Abstract

Query optimizers rely on accurate estimations of the sizes of intermediate results. Wrong size estimations can lead to overly expensive execution plans. We first define the q-error to measure deviations of size estimates from actual sizes. The q-error enables the derivation of two important results: (1) We provide bounds such that if the q-error is smaller than this bound, the query optimizer constructs an optimal plan. (2) If the q-error is bounded by a number q, we show that the cost of the produced plan is at most a factor of q4 worse than the optimal plan. Motivated by these findings, we next show how to find the best approximation under the q-error. These techniques can then be used to build synopsis for size estimates. Finally, we give some experimental results where we apply the developed techniques.