How Good Are Query Optimizers, Really?

Viktor Leis, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, Thomas Neumann

Research output: Chapter in Book / Report / Conference proceedingChapterAcademicpeer-review

Abstract

Finding a good join order is crucial for query performance. In this paper, we introduce the Join Order Benchmark (JOB) and experimentally revisit the main components in the classic query optimizer architecture using a complex, real-world data set and realistic multi-join queries. We investigate the quality of industrial-strength cardinality estimators and find that all estimators routinely produce large errors. We further show that while estimates are essential for finding a good join order, query performance is unsatisfactory if the query engine relies too heavily on these estimates. Using another set of experiments that measure the impact of the cost model, we find that it has much less influence on query performance than the cardinality estimates. Finally, we investigate plan enumeration techniques comparing exhaustive dynamic programming with heuristic algorithms and find that exhaustive enumeration improves performance despite the sub-optimal cardinality estimates.

Original languageEnglish
Title of host publicationProceedings of the VLDB Endowment
PublisherAssociation for Computing Machinery (ACM)
Pages204-215
Number of pages12
Volume9
Edition3
Publication statusPublished - 2016
Event42nd International Conference on Very Large Data Bases, VLDB 2016 - Delhi, India
Duration: 5 Sep 20169 Sep 2016

Conference

Conference42nd International Conference on Very Large Data Bases, VLDB 2016
Country/TerritoryIndia
CityDelhi
Period5/09/169/09/16

Fingerprint

Dive into the research topics of 'How Good Are Query Optimizers, Really?'. Together they form a unique fingerprint.

Cite this