Predicting query execution time: Are optimizer cost models really unusable?

Abstract

Abstract-Predicting query execution time is useful in many database management issues including admission control, query scheduling, progress monitoring, and system sizing. Recently the research community has been exploring the use of statistical machine learning approaches to build predictive models for this task. An implicit assumption behind this work is that the cost models used by query optimizers are insufficient for query execution time prediction. In this paper we challenge this assumption and show while the simple approach of scaling the optimizer's estimated cost indeed fails, a properly calibrated optimizer cost model is surprisingly effective. However, even a well-tuned optimizer cost model will fail in the presence of errors in cardinality estimates. Accordingly we investigate the novel idea of spending extra resources to refine estimates for the query plan after it has been chosen by the optimizer but before execution. In our experiments we find that a well calibrated query optimizer model along with cardinality estimation refinement provides a low overhead way to provide estimates that are always competitive and often much better than the best reported numbers from the machine learning approaches.

Upstream: Exposing Performance Information from Cloud Providers to Tenants

“…Another relevant example of combining our model with domain-specific models is in database systems. Although most of these systems implement their specific cost models for optimization, performance prediction is still a problematic matter in this field, even for dedicated hardware [4,16,17]. Especially for CPU-intensive queries and in-memory databases, sharing CPU time with other tenants may further impair the performance predictability in addition to compromise several administrative and tuning decisions.…”

A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration

“…Karampaglis et al [46] first propose a bi-objective query cost model, which is used to derive running time and monetary cost together in the multi-cloud environment. They model the execution time based on the method in [98]. For economic cost estimation, they first model the charging policies and estimate the monetary cost by combining the policy and time estimation.…”

“…The performance of the one query mainly refers to the latency. Wu et al [98] adopt an offline profiling to calibrate the coefficients in the cost model under a specific hardware and software conditions. Then, they adopt the sampling method to obtain the true cardinalities of the physical operators to predict the execution times.…”

Development of Query Transformation Method by Cost Optimization

“…GarciaMolina et al [1] and Grief et al explored similar methods to eliminate nesting sub-queries SPJ-actively studied for solving sub-queries with aggregates. Chaudhuri [5] and Gupta [7] demonstrated conversion assemblies that are pushed into the position preceding the connection. Chaudhuri [1] examined whether a greedy and conservative heuristics leads to cheaper scanning or connection.…”

“…Chaudhuri [1] examined whether a greedy and conservative heuristics leads to cheaper scanning or connection. Gupta [7] demonstrated a canonical abstraction external connection, which allows the optimizer to use different formations among tables joined externally and internally.…”