Abstract:The query optimization phase within a database management system (DBMS) ostensibly finds the fastest query execution plan from a potentially large set of enumerated plans, all of which correctly compute the same result of the specified query. Sometimes the cost-based optimizer selects a slower plan, for a variety of reasons. Previous work has focused on increasing the performance of specific components, often a single operator, within an individual DBMS. However, that does not address the fundamental question: from where does this suboptimality arise, across DBMSes generally? In particular, the contribution of each of many possible factors to DBMS suboptimality is currently unknown. To identify the root causes of DBMS suboptimality, we first introduce the notion of empirical suboptimality of a query plan chosen by the DBMS, indicated by the existence of a query plan that performs more efficiently than the chosen plan, for the same query. A crucial aspect is that this can be measured externally to the DBMS, and thus does not require access to its source code. We then propose a novel predictive model to explain the relationship between various factors in query optimization and empirical suboptimality. Our model associates suboptimality with the factors of complexity of the schema, of the underlying data on which the query is evaluated, of the query itself, and of the DBMS optimizer. The model also characterizes concomitant interactions among these factors. This model induces a number of specific hypotheses that were tested on multiple DBMSes. We performed a series of experiments that examined the plans for thousands of queries run on four popular DBMSes. We tested the model on over a million of these query executions, using correlational analysis, regression analysis, and causal analysis, specifically Structural Equation Modeling (SEM). We observed that the dependent construct of empirical suboptimality prevalence correlates positively with nine specific constructs characterizing four identified factors that explain in concert much of the variance of suboptimality of two extensive benchmarks, across these disparate DBMSes. This predictive model shows that it is the common aspects of these DBMSes that predict suboptimality, not the particulars embedded in the inordinate complexity of each of these DBMSes. This paper thus provides a new methodology to study mature query optimizers, identifies underlying DBMS-independent causes for the observed suboptimality, and quantifies the relative contribution of each of these causes to the observed suboptimality. This work thus provides a roadmap for fundamental improvements of cost-based query optimizers.

Cost-based or Learning-based? A Hybrid Query Optimizer for Query Plan Selection

Cost-Based or Learning-Based?

COOOL: A Learning-To-Rank Approach for SQL Hint Recommendations

GenJoin: Conditional Generative Plan-to-Plan Query Optimizer that Learns from Subplan Hints

Efficient Query Re-optimization with Judicious Subquery Selections

Learned Query Optimization by Constraint-Based Query Plan Augmentation

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

Learned Query Optimizers: Evaluation and Improvement

Lero: applying learning-to-rank in query optimizer

AQUA+: Query Optimization for Hybrid Database-MapReduce System.

Joint Optimization of Cost and Coverage of Query Plans in Data Integration

Hydro: Adaptive Query Processing of ML Queries

Optimizing subgraph queries by combining binary and worst-case optimal joins

BitE : Accelerating Learned Query Optimization in a Mixed-Workload Environment

Learning to Optimize Join Queries With Deep Reinforcement Learning

PARQO: Penalty-Aware Robust Plan Selection in Query Optimization

Identifying the Root Causes of DBMS Suboptimality

Adaptive Cost Model for Query Optimization

LOGER: A Learned Optimizer Towards Generating Efficient and Robust Query Execution Plans

A Fast Randomized Algorithm for Multi-Objective Query Optimization

A latency and fault-tolerance optimizer for online parallel query plans