Zobrazit v EDS

How to Optimize SQL Queries? A Comparison Between Split, Holistic, and Hybrid Approaches

Uloženo v:
Podrobná bibliografie
Název: How to Optimize SQL Queries? A Comparison Between Split, Holistic, and Hybrid Approaches
Autoři: Luca Gretscher, Jens Dittrich
Zdroj: Proceedings of the VLDB Endowment. 18:3910-3922
Informace o vydavateli: Association for Computing Machinery (ACM), 2025.
Rok vydání: 2025
Popis: Relational database systems internally construct a physical query execution plan (QEP) that specifies exactly how to compute a desired result. However, choosing a QEP involves determining a specific join order, deciding how to access base relations, specifying concrete physical implementations to compute the algebraic operations defined by the given SQL query, and much more. In general, choosing the optimal QEP w.r.t. a predefined cost model is a hard optimization task, referred to as query optimization problem (QOP), that requires super-exponential time in the worst-case. Even though query optimization is a fundamental problem that has been studied for decades now, related work often focuses only on a specific subtask like join ordering. Furthermore, by inspecting open-source database systems, fundamentally different query optimization strategies can be observed. These strategies exhibit vastly different optimization times while having a major impact on the resulting QEP qualities. In this work, we revisit two conceptually different approaches to solve query optimization, namely split and holistic. We discuss their advantages and disadvantages and present a detailed experimental evaluation in our research database system mu t able. Additionally, we propose a hybrid strategy called top-k that is able to rediscover the holistically optimal QEPs while being significantly closer to the optimization time of split.
Druh dokumentu: Article
Jazyk: English
ISSN: 2150-8097
DOI: 10.14778/3749646.3749663
Přístupové číslo: edsair.doi...........9f623eec7b16bc4c3daf7f9d0e38d549
Databáze: OpenAIRE
Popis
Abstrakt:Relational database systems internally construct a physical query execution plan (QEP) that specifies exactly how to compute a desired result. However, choosing a QEP involves determining a specific join order, deciding how to access base relations, specifying concrete physical implementations to compute the algebraic operations defined by the given SQL query, and much more. In general, choosing the optimal QEP w.r.t. a predefined cost model is a hard optimization task, referred to as query optimization problem (QOP), that requires super-exponential time in the worst-case. Even though query optimization is a fundamental problem that has been studied for decades now, related work often focuses only on a specific subtask like join ordering. Furthermore, by inspecting open-source database systems, fundamentally different query optimization strategies can be observed. These strategies exhibit vastly different optimization times while having a major impact on the resulting QEP qualities. In this work, we revisit two conceptually different approaches to solve query optimization, namely split and holistic. We discuss their advantages and disadvantages and present a detailed experimental evaluation in our research database system mu t able. Additionally, we propose a hybrid strategy called top-k that is able to rediscover the holistically optimal QEPs while being significantly closer to the optimization time of split.
ISSN:21508097
DOI:10.14778/3749646.3749663