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

Query optimizer is at the heart of the database systems. Cost-based optimizer studied in this paper is adopted in almost all current database systems. A cost-based optimizer introduces a plan enumeration algorithm to find a (sub)plan, and then uses a cost model to obtain the cost of that plan, and s...

Ausführliche Beschreibung

Gespeichert in:
Bibliographische Detailangaben
Veröffentlicht in:Data Science and Engineering Jg. 6; H. 1; S. 86 - 101
Hauptverfasser: Lan, Hai, Bao, Zhifeng, Peng, Yuwei
Format: Journal Article
Sprache:Englisch
Veröffentlicht: Singapore Springer Singapore 01.03.2021
Springer
Springer Nature B.V
Schlagworte:
Algorithm Analysis and Problem Complexity
Algorithms
Artificial Intelligence
Chemistry and Earth Sciences
Computer Science
Data Mining and Knowledge Discovery
Database Management
Economic aspects
Enumeration
Physics
Queries
Statistics for Engineering
Surveys
Systems and Data Security
Canada
Cardinality estimation
Query optimizer
Cost model
Plan enumeration
ISSN:2364-1185, 2364-1541
Online-Zugang:Volltext
Tags: Tag hinzufügen
Keine Tags, Fügen Sie den ersten Tag hinzu!
Beschreibung
Zusammenfassung:Query optimizer is at the heart of the database systems. Cost-based optimizer studied in this paper is adopted in almost all current database systems. A cost-based optimizer introduces a plan enumeration algorithm to find a (sub)plan, and then uses a cost model to obtain the cost of that plan, and selects the plan with the lowest cost. In the cost model, cardinality, the number of tuples through an operator, plays a crucial role. Due to the inaccuracy in cardinality estimation, errors in cost model, and the huge plan space, the optimizer cannot find the optimal execution plan for a complex query in a reasonable time. In this paper, we first deeply study the causes behind the limitations above. Next, we review the techniques used to improve the quality of the three key components in the cost-based optimizer, cardinality estimation, cost model, and plan enumeration. We also provide our insights on the future directions for each of the above aspects.
Bibliographie:ObjectType-Article-1
SourceType-Scholarly Journals-1
ObjectType-Feature-2
content type line 14
ISSN:2364-1185
2364-1541
DOI:10.1007/s41019-020-00149-7