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.
Additional Metadata
Keywords query optimization
THEME Information (theme 2)
Journal Proceedings of the VLDB Endowment
Project Actian CWI Research Grant
Grant This work was funded by the CWI PPS samenwerking; grant id pps/05050504 - Actian CWI Research Grant
Citation
Leis, V, Gubichev, A, Mirchev, A, Boncz, P.A, Neumann, T, & Kemper, A. (2015). How Good Are Query Optimizers, Really?. Proceedings of the VLDB Endowment, 9(3), 204–215.

Additional Files
24379B.pdf Author Manuscript , 1mb