Testing the accuracy of query optimizers

Abstract

The accuracy of a query optimizer is intricately connected with a database system performance and its operational cost: the more accurate the optimizer's cost model, the better the resulting execution plans. Database application programmers and other practitioners have long provided anecdotal evidence that database systems differ widely with respect to the quality of their optimizers, yet, to date no formal method is available to database users to assess or refute such claims.In this paper, we develop a framework to quantify an optimizer's accuracy for a given workload. We make use of the fact that optimizers expose switches or hints that let users influence the plan choice and generate plans other than the default plan. Using these implements, we force the generation of multiple alternative plans for each test case, time the execution of all alternatives and rank the plans by their effective costs. We compare this ranking with the ranking of the estimated cost and compute a score for the accuracy of the optimizer.We present initial results of an anonymized comparisons for several major commercial database systems demonstrating that there are in fact substantial differences between systems. We also suggest ways to incorporate this knowledge into the commercial development process.

Towards a learning optimizer for shared clouds

“…Traditional query optimizers incorporate a number of heuristics to estimate the cardinalities for each candidate query subgraph. Unfortunately, these heuristics often produce inaccurate estimates, leading to significantly poor performance [17,26,39]. Given large prior workloads in production systems, the question is whether we can learn models to predict the cardinalities.…”

“…The root of all evil, the Achilles Heel of query optimization, is the estimation of the size of intermediate results, known as cardinalities. [27] While it is well-known that poor cardinality estimation leads to inaccuracy in traditional query optimizers [17,26,18], the problem is even harder with big data systems. This is due to: (i) massive volumes of data which are very expensive to analyze and collect statistics on, (ii) presence of unstructured data that have schema imposed at runtime and hence cannot be analyzed a priori, and (iii) pervasive * Work done while at Microsoft.…”

Orca

“…Orca includes a built-in tool called TAQO [15] for Testing the Accuracy of Query Optimizer. TAQO measures the ability of optimizer's cost model to order any two given plans correctly, i.e., the plan with the higher estimated cost will indeed run longer.…”

“…The correlation score also allows benchmarking the optimizers of different database systems to evaluate their relative quality. We discuss the testing methodology implemented in TAQO in more detail in [15].…”

“…e.g., [9,18,20,21]), which makes large datasets of hundreds of terabytes or even petabytes readily accessible for analysis through SQL or SQL-like interfaces. Differences between good and mediocre optimizers have always been known to be substantial [15]. However, the increased amount of data these systems have to process magnifies optimization mistakes and stresses the importance of query optimization more than ever.…”