research-article

Research challenges in deep reinforcement learning-based join query optimization

Authors:

Runsheng Benson Guo,

Khuzaima DaudjeeAuthors Info & Claims

aiDM '20: Proceedings of the Third International Workshop on Exploiting Artificial Intelligence Techniques for Data Management

Article No.: 3, Pages 1 - 6

https://doi.org/10.1145/3401071.3401657

Published: 14 June 2020 Publication History

Abstract

The order in which relations are joined and the physical join operators used are two aspects of query plans which have a significant impact on the execution latency of join queries. However, the set of valid query plans grows exponentially with the number of relations to be joined. Hence, it becomes computationally expensive to enumerate all such plans for a complex join query. Recently, several deep reinforcement learning (DRL) based approaches propose using neural networks to construct a query plan. They demonstrate that efficient query plans can be found without exhaustively enumerating the search space. We integrated our implementation of a DRL-based solution to optimize join order and operators into the PostgreSQL query optimizer. In practice, we found limitations in the quality of the query plans chosen which are not addressed in existing approaches. In this paper we highlight some of these limitations and propose future research challenges along with potential solutions.

References

[1]

2020. PostgreSQL database. http://www.postgresql.org/

[2]

2020. PostgreSQL: Genetic Query Optimizer. https://www.postgresql.org/docs/12/static/geqo.html

[3]

2020. SQL Server 2019. https://www.microsoft.com/en-ca/sql-server/sql-server-2019

[4]

Iqbal Alvi. 2019. Transactional vs. Analytical Databases: How Does OLTP Differ from OLAP. https://datawarehouseinfo.com/how-does-oltp-differ-from-olap-database/

[5]

R. Bisiani. 1987. Beam Search. In Encyclopedia of Artificial Intelligence, S. Shapiro (Ed.). Wiley & Sons, 56--58.

[6]

Surajit Chaudhuri. 1998. An Overview of Query Optimization in Relational Systems. In Proceedings of the Seventeenth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems (Seattle, Washington, USA) (PODS '98). Association for Computing Machinery, New York, NY, USA, 34--43.

Digital Library

[7]

Andreas Kipf, Thomas Kipf, Bernhard Radke, Viktor Leis, Peter A. Boncz, and Alfons Kemper. 2019. Learned Cardinalities: Estimating Correlated Joins with Deep Learning. In CIDR 2019, 9th Biennial Conference on Innovative Data Systems Research, Asilomar, CA, USA, January 13-16, 2019, Online Proceedings. www.cidrdb.org. http://cidrdb.org/cidr2019/papers/p101-kipf-cidr19.pdf

[8]

Sanjay Krishnan, Zongheng Yang, Ken Goldberg, Joseph M. Hellerstein, and Ion Stoica. 2018. Learning to Optimize Join Queries With Deep Reinforcement Learning. CoRR abs/1808.03196 (2018). arXiv:1808.03196 http://arxiv.org/abs/1808.03196

[9]

Viktor Leis, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, and Thomas Neumann. 2015. How Good Are Query Optimizers, Really? Proc. VLDB Endow. 9, 3 (Nov. 2015), 204--215.

Digital Library

[10]

Henry Liu, Mingbin Xu, Ziting Yu, Vincent Corvinelli, and Calisto Zuzarte. 2015. Cardinality Estimation Using Neural Networks. In Proceedings of the 25th Annual International Conference on Computer Science and Software Engineering (Markham, Canada) (CASCON '15). IBM Corp., USA, 53--59.

Digital Library

[11]

Ryan Marcus, Parimarjan Negi, Hongzi Mao, Chi Zhang, Mohammad Alizadeh, Tim Kraska, Olga Papaemmanouil, and Nesime Tatbul. 2019. Neo: A Learned Query Optimizer. Proc. VLDB Endow. 12, 11 (July 2019), 1705--1718.

Digital Library

[12]

Ryan Marcus and Olga Papaemmanouil. 2018. Deep Reinforcement Learning for Join Order Enumeration. In Proceedings of the First International Workshop on Exploiting Artificial Intelligence Techniques for Data Management (Houston, TX, USA) (aiDM'18). Association for Computing Machinery, New York, NY, USA, Article 3, 4 pages.

Digital Library

[13]

Ryan Marcus and Olga Papaemmanouil. 2018. Towards a Hands-Free Query Optimizer through Deep Learning. CoRR abs/1809.10212 (2018). arXiv:1809.10212 http://arxiv.org/abs/1809.10212

[14]

Ryan Marcus and Olga Papaemmanouil. 2019. Plan-Structured Deep Neural Network Models for Query Performance Prediction. Proc. VLDB Endow. 12, 11 (July 2019), 1733--1746.

Digital Library

[15]

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. In Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data (Boston, Massachusetts) (SIGMOD '79). Association for Computing Machinery, New York, NY, USA, 23--34.

Digital Library

[16]

Connor Shorten and Taghi Khoshgoftaar. 2019. A survey on Image Data Augmentation for Deep Learning. Journal of Big Data 6 (12 2019).

[17]

Chenggang Wu, Alekh Jindal, Saeed Amizadeh, Hiren Patel, Wangchao Le, Shi Qiao, and Sriram Rao. 2018. Towards a Learning Optimizer for Shared Clouds. Proc. VLDB Endow. 12, 3 (Nov. 2018), 210--222.

Digital Library

[18]

Chengliang Chai Nan Tang Xiang Yu, Guoliang Li. 2020. Reinforcement Learning with Tree-LSTM for Join Order Selection. In 36th IEEE International Conference on Data Engineering, ICDE 2020, Dallas, USA, April 20-124, 2020. IEEE, 1297--1308.

Cited By

Ramadan MMokhtar HSobh IEl-Kilany A(2024)Towards online training for RL-based query optimizerInternational Journal of Data Science and Analytics10.1007/s41060-024-00628-4Online publication date: 10-Sep-2024
https://doi.org/10.1007/s41060-024-00628-4
Ji LZhao RDang YLiu JZhang H(2023)Query Join Order Optimization Method Based on Dynamic Double Deep Q-NetworkElectronics10.3390/electronics1206150412:6(1504)Online publication date: 22-Mar-2023
https://doi.org/10.3390/electronics12061504
Chikhaoui AChadli AOuared A(2023)A model-based DevOps process for development of mathematical database cost modelsAutomated Software Engineering10.1007/s10515-023-00390-030:2Online publication date: 28-Jul-2023
https://dl.acm.org/doi/10.1007/s10515-023-00390-0
Show More Cited By

Research challenges in deep reinforcement learning-based join query optimization
1. Information systems
  1. Data management systems
    1. Database management system engines
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory

Recommendations

Deep Reinforcement Learning for Join Order Enumeration
aiDM'18: Proceedings of the First International Workshop on Exploiting Artificial Intelligence Techniques for Data Management

Join order selection plays a significant role in query performance. However, modern query optimizers typically employ static join order enumeration algorithms that do not incorporate feedback about the quality of the resulting plan. Hence, optimizers ...
Coral: federated query join order optimization based on deep reinforcement learning
Abstract
The rise of diversified data engines has created the need for federated queries. A federated query can take a query and provide data analysis based on data from various data engines. Since the query data originates from multiple data engines, ...
Distributed stream join query processing with semijoins

This paper addresses the distributed stream processing of window-based multi-way join queries considering the semijoin as a key join operator. In distributed stream processing, data streams arriving at remote sites need to be shipped to the processing ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

aiDM '20: Proceedings of the Third International Workshop on Exploiting Artificial Intelligence Techniques for Data Management

June 2020

33 pages

ISBN:9781450380294

DOI:10.1145/3401071

Conference Chairs:
Rajesh Bordawekar
IBM T. J. Watson Research Center
,
Oded Shmueli
Technion
,
Nesime Tatbul
MIT and Intel Labs
,
Tin Kam Ho
IBM

Copyright © 2020 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 ACM 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: 14 June 2020

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Qualifiers

Research-article

Conference

SIGMOD/PODS '20

Sponsor:

SIGMOD

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

June 14 - 20, 2020

Oregon, Portland

Acceptance Rates

aiDM '20 Paper Acceptance Rate 6 of 6 submissions, 100%;

Overall Acceptance Rate 19 of 26 submissions, 73%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

10
Total Citations
View Citations
412
Total Downloads

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

Reflects downloads up to 14 Sep 2024

Other Metrics

View Author Metrics

Citations

Cited By

Ramadan MMokhtar HSobh IEl-Kilany A(2024)Towards online training for RL-based query optimizerInternational Journal of Data Science and Analytics10.1007/s41060-024-00628-4Online publication date: 10-Sep-2024
https://doi.org/10.1007/s41060-024-00628-4
Ji LZhao RDang YLiu JZhang H(2023)Query Join Order Optimization Method Based on Dynamic Double Deep Q-NetworkElectronics10.3390/electronics1206150412:6(1504)Online publication date: 22-Mar-2023
https://doi.org/10.3390/electronics12061504
Chikhaoui AChadli AOuared A(2023)A model-based DevOps process for development of mathematical database cost modelsAutomated Software Engineering10.1007/s10515-023-00390-030:2Online publication date: 28-Jul-2023
https://dl.acm.org/doi/10.1007/s10515-023-00390-0
Liu HPeng ZZhang ZJiang HPeng Y(2023)MSP: Learned Query Performance Prediction Using MetaInfo and Structure of PlansWeb and Big Data10.1007/978-3-031-25201-3_1(3-18)Online publication date: 10-Feb-2023
https://doi.org/10.1007/978-3-031-25201-3_1
Ouared AAmrani MSchobbens P(2023)Explainable AI for DBA: Bridging the DBA's experience and machine learning in tuning database systemsConcurrency and Computation: Practice and Experience10.1002/cpe.769835:21Online publication date: 31-Mar-2023
https://doi.org/10.1002/cpe.7698
Abebe MLazu HDaudjee K(2022)TiresiasProceedings of the VLDB Endowment10.14778/3551793.355185715:11(3126-3136)Online publication date: 29-Sep-2022
https://dl.acm.org/doi/10.14778/3551793.3551857
Marcus RNegi PMao HTatbul NAlizadeh MKraska T(2022)BaoACM SIGMOD Record10.1145/3542700.354270351:1(6-13)Online publication date: 1-Jun-2022
https://dl.acm.org/doi/10.1145/3542700.3542703
Ramadan MEl-Kilany AMokhtar HSobh I(2022)RL_QOptimizer: A Reinforcement Learning Based Query OptimizerIEEE Access10.1109/ACCESS.2022.318710210(70502-70515)Online publication date: 2022
https://doi.org/10.1109/ACCESS.2022.3187102
Marcus RNegi PMao HTatbul NAlizadeh MKraska TLi GLi ZIdreos SSrivastava D(2021)Bao: Making Learned Query Optimization PracticalProceedings of the 2021 International Conference on Management of Data10.1145/3448016.3452838(1275-1288)Online publication date: 9-Jun-2021
https://dl.acm.org/doi/10.1145/3448016.3452838
Snodgrass RCurrim SSuh Y(2021)Have query optimizers hit the wall?The VLDB Journal10.1007/s00778-021-00689-yOnline publication date: 20-Sep-2021
https://doi.org/10.1007/s00778-021-00689-y

View Options

Get Access

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