Estimating compilation time of a query optimizer

Abstract

A query optimizer compares alternative plans in its search space to find the best plan for a given query. Depending on the search space and the enumeration algorithm, optimizers vary in their compilation time and the quality of the execution plan they can generate. We build a compilation time estimator that provides a quantified estimate of the optimizer compilation time for a given query. Such an estimator is useful for automatically choosing the right level of optimization in commercial database systems. In addition, compilation time estimates can be quite helpful for midquery reoptimization, for monitoring the progress of workload analysis tools where a large number queries need to be compiled (but not executed), and for judicious design and tuning of an optimizer.Previous attempts to estimate optimizer compilation complexity used the number of possible binary join sequences as the metric and overlooked the fact that each join sequence often translates into a different number of join plans because of the presence of "physical" properties. We use the number of plans (instead of join sequences) to estimate query compilation time and employ two novel ideas: (1) reusing an optimizer's join enumerator to obtain accurate number of join sequences, but bypassing plan generation to save estimation overhead; (2) maintaining a small number of "interesting" properties to facilitate plan counting. We prototyped our approach in a commercial database system and our experimental results show that we can achieve good compilation time estimates (less than 30% error, on average) for complex real queries, using a small fraction (within 3%) of the actual compilation time.

Dependency-aware reordering for parallelizing query optimization in multi-core CPUs

“…OLTP queries are not our focus, since they can be optimized very fast. An experimental study on a real DB2 query workload [18] verified that compilation time is dominated by the number of join re-orderings. Thus, we believe that star queries (for varying the number of joins) indeed model representative real data warehouse queries and are sufficient to show our claim.…”

“…To avoid evaluating redundant subplans, the bottom-up enumerator exploits the principle of optimality and stores the optimal QEPs in an in-memory quantifier set table (a.k.a. MEMO) [18]. Each entry in MEMO contains a list of QEPs for a quantifier set, and MEMO is typically implemented by using a hash table with the quantifier set as the key.…”

OptMark

“…An interesting observation is that the r 2 value of # PP and # PJ, and that of # LP and # JO are very close to, or even identical to each other on all the DBMSs. The reason is that non-join plans (e.g., Table 1: Correlation of efficiency metrics and optimization times scan plans and index scan plans) are typically much fewer than join plans, and thus take much less optimization time [13].…”

Parallelizing query optimization

“…SerialDP Enum generates query execution plans (QEPs) in a "bottom up" fashion [12]. It first generates different QEPs for accessing a single table.…”

“…Many commercial RDBMSs such as DB2 employ dynamic programming (DP), pioneered by Selinger et al [29]. Dynamic programming builds QEPs "bottom up" and exploits the principle of optimality to prune sub-optimal plans at each iteration (thereby saving space) and to guarantee that the optimal QEP is found without evaluating redundant sub-plans [12].…”