research-article

On the Calculation of Optimality Ranges for Relational Query Execution Plans

Authors:

Paul R. Willems,

Kai-Uwe SattlerAuthors Info & Claims

SIGMOD '18: Proceedings of the 2018 International Conference on Management of Data

Pages 663 - 675

https://doi.org/10.1145/3183713.3183742

Published: 27 May 2018 Publication History

Abstract

Cardinality estimation is a crucial task in query optimization and typically relies on heuristics and basic statistical approximations. At execution time, estimation errors might result in situations where intermediate result sizes may differ from the estimated ones, so that the originally chosen plan is not the optimal plan anymore. In this paper we analyze the deviation from the estimate, and denote the cardinality range of an intermediate result, where the optimal plan remains optimal as the optimality range. While previous work used simple heuristics to calculate similar ranges, we generate the precise bounds for the optimality range considering all relevant plan alternatives. Our experimental results show that the fixed optimality ranges used in previous work fail to characterize the range of cardinalities where a plan is optimal. We derive theoretical worst case bounds for the number of enumerated plans required to compute the precise optimality range, and experimentally show that in real queries this number is significantly smaller. Our experiments also show the benefit for applications like Mid-Query Re-Optimization in terms of significant execution time improvement.

References

[1]

Shivnath Babu, Pedro Bizarro, and David DeWitt. 2005. Proactive Re-optimization. In Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data (SIGMOD '05). ACM, New York, NY, USA.

Digital Library

[2]

Pedro Bizarro, Nicolas Bruno, and David J. DeWitt. 2009. Progressive Parametric Query Optimization. IEEE Transactions on Knowledge and Data Engineering Vol. 21, 4 (April. 2009).

Digital Library

[3]

Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, and Clifford Stein . 2009. Introduction to Algorithms, Third Edition (bibinfoedition3rd ed.). The MIT Press.

Digital Library

[4]

Transaction Processing Performance Council. 2017. TPC Benchmark H (Decision Support) Standard Specification Revision 2.17.2. http://www.tpc.org. (2017).

[5]

Harish D, Pooja N. Darera, and Jayant R. Haritsa. 2007. On the Production of Anorexic Plan Diagrams. In Proceedings of the 33rd International Conference on Very Large Data Bases (VLDB '07). VLDB Endowment.

Digital Library

[6]

Harish D., Pooja N. Darera, and Jayant R. Haritsa. 2008. Identifying Robust Plans Through Plan Diagram Reduction. Proceedings of the VLDB Endowment Vol. 1, 1 (Aug. 2008).

Digital Library

[7]

David DeHaan and Frank Wm. Tompa. 2007. Optimal Top-down Join Enumeration. In Proceedings of the 2007 ACM SIGMOD International Conference on Management of Data (SIGMOD '07). ACM, New York, NY, USA.

Digital Library

[8]

Pit Fender and Guido Moerkotte. 2013. Counter Strike: Generic Top-down Join Enumeration for Hypergraphs. Proc. VLDB Endow. Vol. 6, 14 (Sept. 2013).

Digital Library

[9]

Goetz Graefe and William J. McKenna. 1993. The Volcano Optimizer Generator: Extensibility and Efficient Search Proceedings of the Ninth International Conference on Data Engineering. IEEE Computer Society, Washington, DC, USA.

Digital Library

[10]

Arvind Hulgeri and S. Sudarshan. 2002. Parametric Query Optimization for Linear and Piecewise Linear Cost Functions Proceedings of the 28th International Conference on Very Large Data Bases (VLDB '02). VLDB Endowment.

Digital Library

[11]

Navin Kabra and David J. DeWitt. 1998. Efficient Mid-query Re-optimization of Sub-optimal Query Execution Plans Proceedings of the 1998 ACM SIGMOD International Conference on Management of Data (SIGMOD '98). ACM, New York, NY, USA.

Digital Library

[12]

Alfons Kemper and Thomas Neumann. 2011. HyPer: A Hybrid OLTP&OLAP Main Memory Database System Based on Virtual Memory Snapshots. In Proceedings of the 2011 IEEE 27th International Conference on Data Engineering (ICDE '11). IEEE Computer Society, Washington, DC, USA.

Digital Library

[13]

Viktor Leis, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, and Thomas Neumann. 2015. How Good Are Query Optimizers, Really? Proceedings of the VLDB Endowment Vol. 9, 3 (Nov. 2015).

Digital Library

[14]

Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, and Miso Cilimdzic. 2004. Robust Query Processing Through Progressive Optimization Proceedings of the 2004 ACM SIGMOD International Conference on Management of Data (SIGMOD '04). ACM, New York, NY, USA.

Digital Library

[15]

Norman May, Alexander Böhm, and Wolfgang Lehner. 2017. SAP HANA -- The Evolution of an In-Memory DBMS from Pure OLAP Processing Towards Mixed Workloads. In Datenbanksysteme für Business, Technologie und Web (BTW '17). Gesellschaft für Informatik, Bonn.

[16]

Guido Moerkotte. 2014. Building Query Compilers. (2014). deftempurl%http://pi3.informatik.uni-mannheim.de/moerkotte.html.en tempurl unpublished.

[17]

Guido Moerkotte and Thomas Neumann. 2008. Dynamic Programming Strikes Back. In Proceedings of the 2008 ACM SIGMOD International Conference on Management of Data (SIGMOD '08). ACM, New York, NY, USA.

Digital Library

[18]

Kiyoshi Ono and Guy M. Lohman. 1990. Measuring the Complexity of Join Enumeration in Query Optimization Proceedings of the 16th International Conference on Very Large Data Bases (VLDB '90). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA.

Digital Library

[19]

Arjan Pellenkoft, César A. Galindo-Legaria, and Martin L. Kersten. 1997. The Complexity of Transformation-Based Join Enumeration Proceedings of the 23rd International Conference on Very Large Data Bases (VLDB '97). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA.

Digital Library

[20]

Iraklis Psaroudakis, Florian Wolf, Norman May, Thomas Neumann, Alexander Böhm, Anastasia Ailamaki, and Kai-Uwe Sattler. 2014. Scaling up Mixed Workloads: A Battle of Data Freshness, Flexibility, and Scheduling. In Technology Conference on Performance Evaluation and Benchmarking (TPCTC '14). Springer International Publishing.

[21]

P. Griffiths Selinger, M. M. Astrahan, D. D. Chamberlin, R. A. Lorie, and T. G. Price. 1979. Access Path Selection in a Relational Database Management System Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data (SIGMOD '79). ACM, New York, NY, USA.

Digital Library

[22]

Michael Stillger, Guy M. Lohman, Volker Markl, and Mokhtar Kandil. 2001. LEO - DB2's LEarning Optimizer. In Proceedings of the 27th International Conference on Very Large Data Bases (VLDB '01). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA.

Digital Library

[23]

Shaoyi Yin, Abdelkader Hameurlain, and Franck Morvan. 2015. Robust Query Optimization Methods With Respect to Estimation Errors: A Survey. SIGMOD Record Vol. 44, 3 (Dec. 2015).

Digital Library

Cited By

Kamali AKantere VZuzarte C(2024)Robust Query Optimization in the Era of Machine Learning: State-of-the-Art and Future Directions2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00408(5371-5375)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00408
Zhao JZhang HGao Y(2023)Efficient Query Re-optimization with Judicious Subquery SelectionsProceedings of the ACM on Management of Data10.1145/35893301:2(1-26)Online publication date: 20-Jun-2023
https://dl.acm.org/doi/10.1145/3589330
Li YYu XKoudas NLin SSun CChen C(2023)dbET: Execution Time Distribution-based Plan SelectionProceedings of the ACM on Management of Data10.1145/35887111:1(1-26)Online publication date: 30-May-2023
https://dl.acm.org/doi/10.1145/3588711
Show More Cited By

Index Terms

On the Calculation of Optimality Ranges for Relational Query Execution Plans
1. Information systems
  1. Data management systems
    1. Database design and models
      1. Relational database model
    2. Database management system engines
      1. Database query processing
        Query optimization
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory
      1. Data structures and algorithms for data management
      2. Database query processing and optimization (theory)

Recommendations

Exact Cardinality Query Optimization with Bounded Execution Cost
SIGMOD '19: Proceedings of the 2019 International Conference on Management of Data

Query optimizers often produce sub-optimal query plans due to inaccurate cardinality estimates. The goal of exact cardinality query optimization (ECQO) is to produce optimal plans based on guaranteed exact cardinality values, obtained via probe ...
Improvement Algorithms for Semijoin Query Processing Programs in Distributed Database Systems

The problem of optimal query processing in distributed database systems was shown to be NP-hard. This means that heuristic algorithms are necessary to solve the query processing problem. In this paper, we describe algorithms to improve the solutions ...
Practical planning and execution of groupjoin and nested aggregates
Abstract
Groupjoins combine execution of a join and a subsequent group-by. They are common in analytical queries and occur in about [inline-graphic not available: see fulltext] of the queries in TPC-H and TPC-DS. While they were originally invented to ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '18: Proceedings of the 2018 International Conference on Management of Data

May 2018

1874 pages

ISBN:9781450347037

DOI:10.1145/3183713

General Chairs:
Gautam Das
University of Texas at Arlington, USA
,
Christopher Jermaine
Rice University, USA
,
Philip Bernstein
Microsoft Research, USA

Copyright © 2018 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected].

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 27 May 2018

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS '18

Sponsor:

SIGMOD

SIGMOD/PODS '18: International Conference on Management of Data

June 10 - 15, 2018

TX, Houston, USA

Acceptance Rates

SIGMOD '18 Paper Acceptance Rate 90 of 461 submissions, 20%;

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

7
Total Citations
View Citations
474
Total Downloads

Downloads (Last 12 months)18
Downloads (Last 6 weeks)8

Reflects downloads up to 24 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

Kamali AKantere VZuzarte C(2024)Robust Query Optimization in the Era of Machine Learning: State-of-the-Art and Future Directions2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00408(5371-5375)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00408
Zhao JZhang HGao Y(2023)Efficient Query Re-optimization with Judicious Subquery SelectionsProceedings of the ACM on Management of Data10.1145/35893301:2(1-26)Online publication date: 20-Jun-2023
https://dl.acm.org/doi/10.1145/3589330
Li YYu XKoudas NLin SSun CChen C(2023)dbET: Execution Time Distribution-based Plan SelectionProceedings of the ACM on Management of Data10.1145/35887111:1(1-26)Online publication date: 30-May-2023
https://dl.acm.org/doi/10.1145/3588711
Wang ZZeng KHuang BChen WCui XWang BLiu JFan LQu DHou ZGuan TLi CZhou J(2023)Tempura: a general cost-based optimizer framework for incremental data processing (Journal Version)The VLDB Journal10.1007/s00778-023-00785-132:6(1315-1342)Online publication date: 20-Mar-2023
https://doi.org/10.1007/s00778-023-00785-1
АльМусави ОКравец О(2022)Algorithmization of repeated query optimization in cloud databases with the aid of computer trainingМОДЕЛИРОВАНИЕ, ОПТИМИЗАЦИЯ И ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ10.26102/2310-6018/2022.36.1.02010:1(36)(20-21)Online publication date: 25-Jan-2022
https://doi.org/10.26102/2310-6018/2022.36.1.020
Wang ZZeng KHuang BChen WCui XWang BLiu JFan LQu DHou ZGuan TLi CZhou J(2020)TempuraProceedings of the VLDB Endowment10.14778/3421424.342142714:1(14-27)Online publication date: 1-Sep-2020
https://dl.acm.org/doi/10.14778/3421424.3421427
Wolf FBrendle MMay NWillems PSattler KGrossniklaus M(2018)Robustness metrics for relational query execution plansProceedings of the VLDB Endowment10.14778/3236187.323619111:11(1360-1372)Online publication date: 1-Jul-2018
https://dl.acm.org/doi/10.14778/3236187.3236191

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents