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

Lan, Hai; Bao, Zhifeng; Peng, Yuwei

doi:10.1007/s41019-020-00149-7

Cited by 56 publications

(24 citation statements)

References 86 publications

(134 reference statements)

Supporting

Mentioning

Contrasting

Unclassified

Order By: Relevance

“…Logical plan parsing parses a query plan to aggregate key features, including the cost or categories of operators, scanned tables, and predicates. It has been adopted by many state-of-the-art works in the field of query-performance prediction or cardinality estimation [4,5,23,33,38,47,51,58]. QTune encodes query type, involved tables, query operators and the corresponding costs to predict the internal metrics of a database [41].…”

Section: Related Workmentioning

confidence: 99%

Towards Dynamic and Safe Configuration Tuning for Cloud Databases

Zhang,

Wu,

et al. 2022

Preprint

View full text Add to dashboard Cite

Configuration knobs of database systems are essential to achieve high throughput and low latency. Recently, automatic tuning systems using machine learning methods (ML) have shown to find better configurations compared to experienced database administrators (DBAs). However, there are still gaps to apply the existing systems in production environments, especially in the cloud. First, they conduct tuning for a given workload within a limited time window and ignore the dynamicity of workloads and data. Second, they rely on a copied instance and do not consider the availability of the database when sampling configurations, making the tuning expensive, delayed, and unsafe. To fill these gaps, we propose OnlineTune, which tunes the online databases safely in changing cloud environments. To accommodate the dynamicity, OnlineTune embeds the environmental factors as context feature and adopts contextual Bayesian Optimization with context space partition to optimize the database adaptively and scalably. To pursue safety during tuning, we leverage the black-box and the white-box knowledge to evaluate the safety of configurations and propose a safe exploration strategy via subspace adaptation.We conduct evaluations on dynamic workloads from benchmarks and real-world workloads. Compared with the state-of-the-art methods, OnlineTune achieves 14.4%~165.3% improvement on cumulative performance while reducing 91.0%~99.5% unsafe configuration recommendations. CCS CONCEPTS• Information systems → Autonomous database administration; • Computing methodologies → Machine learning.

show abstract

Section: Related Workmentioning

confidence: 99%

Towards Dynamic and Safe Configuration Tuning for Cloud Databases

Zhang,

Wu,

et al. 2022

Preprint

View full text Add to dashboard Cite

show abstract

“…The term "query optimization" refers to the process of determining the most efficient way to execute an SQL statement. This topic has been among the most researched ones in the database systems community for several decades [3], [12] . Still, however, this optimization problem remains unsolved and that is why with the rise of machine learning, there have emerged a lot of applications of ML towards database systems and query optimization problems in particular [29].…”

Section: Related Workmentioning

confidence: 99%

Learned Query Optimizers: Evaluation and Improvement

Mikhaylov

Mazyavkina

Salnikov³

et al. 2022

IEEE Access

View full text Add to dashboard Cite

Query Optimization is considered to be one of the most important challenges in database management. Existing built-in query optimizers are very complex and rely on various approximations and hand-picked rules. The rise of deep learning and deep reinforcement learning has aided many scientific and industrial fields, providing an opportunity to develop a learnable query optimizer. In this paper, we analyse and improve the state-of-the-art learned query optimizer, Neo for the JOB benchmark on two database systems: PostgreSQL and Huawei GaussDB. We describe our methods, based on combination of Neo, Tree-Transformers, auxiliary tasks, reward weighting. Combinations of these methods improve latency of the found query execution plans. We also conduct a thorough analysis of the resulting execution plans and devise a set of decision-based rules to indicate the cases when the learned optimizer will outperform the built-in one. We also provide a source code for the proposed methods and experiments. Finally, we provide possible directions for further improvement in this field.

show abstract

“…This performance is usually quantified by computational models that are known generally as cost models 17 . (for more details, please refer to the survey of References 24).…”

Section: Background and Work Positioningmentioning

confidence: 99%

DeepCM: Deep neural networks to improve accuracy prediction of database cost models

Ouared

Chadli

Daoud³

2021

Concurrency and Computation

View full text Add to dashboard Cite

A major challenge for many database management tasks including admission control, query scheduling, progress monitoring and self-driving data storage systems is to enhance queries performances which are based on computational models known as database cost models. One of the most challenging aspects of developing accurate database cost models is identifying their parameters and capturing their relationships, consequently we can derive the query execution cost on the basis of a specific database hosted on a given platform. Furthermore, the highly dynamic workload (i.e., a set of queries) and the query execution variation lead to performance degradation risk, therefore cost models need to be improved by considering newer software configuration and future workload characteristics. In this article, we propose a framework called DeepCM that is based on a min-max optimization for building robust database cost model against uncertainty parameters. Furthermore, our framework is based on Robust Deep Neural Networks to build database cost models that guarantee a high accuracy regardless of variations from software configuration and workload characteristics.Several experiments have been done to evaluate the robustness of produced cost models and findings show that DeepCM provides a high cost model prediction accuracy and stable performance.

show abstract

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

Cited by 56 publications

References 86 publications

Towards Dynamic and Safe Configuration Tuning for Cloud Databases

Towards Dynamic and Safe Configuration Tuning for Cloud Databases

Learned Query Optimizers: Evaluation and Improvement

DeepCM: Deep neural networks to improve accuracy prediction of database cost models

Contact Info

Product

Resources

About