{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2026,3,19]],"date-time":"2026-03-19T02:22:00Z","timestamp":1773886920392,"version":"3.50.1"},"reference-count":41,"publisher":"Association for Computing Machinery (ACM)","issue":"3","content-domain":{"domain":["dl.acm.org"],"crossmark-restriction":true},"short-container-title":["Proc. VLDB Endow."],"published-print":{"date-parts":[[2024,11]]},"abstract":"<jats:p>Parametric Query Optimization (PQO) is crucial for efficiently handling parametrized queries (PQ) in many database applications. This paper addresses two key challenges in existing PQO techniques, focusing on plan set generation and best plan selection. Regarding plan set generation, existing methods rely on modifying sub-plan cardinalities, often resulting in inefficiency and sub-optimal performance due to unclear extents of modifications needed. To overcome this issue, we propose a hybrid plan enumeration algorithm that adeptly adjusts both cardinality and join order. Regarding best plan selection, recent methods rely on machine learning models to choose plans with minimum predicted latency, but they struggle with accurate predictions when parameter bindings vary. Even minor variations in parameters can significantly impact cardinality, affecting plan optimality. To overcome this issue, we propose to utilize a learning-to-rank model, which uses relative rankings as a more reliable performance indicator. Our approach, integrated into PostgreSQL, undergoes extensive experiments on real datasets, showcasing significant improvements in both efficiency and accuracy, as compared to baselines. Specifically, it accelerates the PostgreSQL optimizer by up to 2.57\u00d7 and surpasses the best existing baseline by up to 1.36\u00d7.<\/jats:p>","DOI":"10.14778\/3712221.3712248","type":"journal-article","created":{"date-parts":[[2025,4,7]],"date-time":"2025-04-07T18:03:04Z","timestamp":1744048984000},"page":"863-875","update-policy":"https:\/\/doi.org\/10.1145\/crossmark-policy","source":"Crossref","is-referenced-by-count":2,"title":["RankPQO: Learning-to-Rank for Parametric Query Optimization"],"prefix":"10.14778","volume":"18","author":[{"given":"Songsong","family":"Mo","sequence":"first","affiliation":[{"name":"Nanyang Technological University, Singapore"}]},{"given":"Yue","family":"Zhao","sequence":"additional","affiliation":[{"name":"Nanyang Technological University, Singapore"}]},{"given":"Zhifeng","family":"Bao","sequence":"additional","affiliation":[{"name":"RMIT University, Melbourne, Australia"}]},{"given":"Quanqing","family":"Xu","sequence":"additional","affiliation":[{"name":"OceanBase, Ant Group, Hangzhou, China"}]},{"given":"Chuanhui","family":"Yang","sequence":"additional","affiliation":[{"name":"OceanBase, Ant Group, Hangzhou, China"}]},{"given":"Gao","family":"Cong","sequence":"additional","affiliation":[{"name":"Nanyang Technological University, Singapore"}]}],"member":"320","published-online":{"date-parts":[[2025,4,7]]},"reference":[{"key":"e_1_2_1_1_1","unstructured":"[n.d.]. OceanBase Plan Cache. https:\/\/en.oceanbase.com\/docs\/common-oceanbase-database-10000000001123504."},{"key":"e_1_2_1_2_1","unstructured":"[n.d.]. SQL Server Plan Cache Object. https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance-monitor\/sql-server-plan-cache-object?view=sql-server-ver15."},{"key":"e_1_2_1_3_1","unstructured":"2022. TPC-H Benchmark. https:\/\/www.tpc.org\/tpc_documents_current_versions\/current_specifications5.asp."},{"key":"e_1_2_1_4_1","unstructured":"2024. Postgres hint plan. https:\/\/github.com\/ossc-db\/pg_hint_plan."},{"key":"e_1_2_1_5_1","volume-title":"Parametric Plan Caching Using Density-Based Clustering. In IEEE 28th International Conference on Data Engineering (ICDE 2012","author":"Alu\u00e7 G\u00fcnes","year":"2012","unstructured":"G\u00fcnes Alu\u00e7, David DeHaan, and Ivan T. Bowman. 2012. Parametric Plan Caching Using Density-Based Clustering. In IEEE 28th International Conference on Data Engineering (ICDE 2012), Washington, DC, USA (Arlington, Virginia), 1-5 April, 2012. IEEE Computer Society, 402--413."},{"key":"e_1_2_1_6_1","first-page":"1","volume-title":"Proc. ACM Manag. Data 2","author":"Aytimur Mehmet","year":"2024","unstructured":"Mehmet Aytimur, Silvan Reiner, Leonard W\u00f6rteler, Theodoros Chondrogiannis, and Michael Grossniklaus. 2024. LPLM: A Neural Language Model for Cardinality Estimation of LIKE-Queries. Proc. ACM Manag. Data 2, 1 (2024), 54:1--54:25."},{"key":"e_1_2_1_7_1","doi-asserted-by":"publisher","DOI":"10.1109\/TKDE.2008.160"},{"key":"e_1_2_1_8_1","volume-title":"Proceedings of the ACM SIGMOD International Conference on Management of Data, SIGMOD 2010","author":"Chaudhuri Surajit","year":"2010","unstructured":"Surajit Chaudhuri, Hongrae Lee, and Vivek R. Narasayya. 2010. Variance aware optimization of parameterized queries. In Proceedings of the ACM SIGMOD International Conference on Management of Data, SIGMOD 2010, Indianapolis, Indiana, USA, June 6-10, 2010. ACM, 531--542."},{"key":"e_1_2_1_9_1","doi-asserted-by":"publisher","DOI":"10.14778\/3484224.3484234"},{"key":"e_1_2_1_10_1","volume-title":"Proceedings of the 33rd International Conference on Very Large Data Bases","author":"Doraiswamy Harish","year":"2007","unstructured":"Harish Doraiswamy, 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, University of Vienna, Austria, September 23-27, 2007. ACM, 1081--1092."},{"key":"e_1_2_1_11_1","first-page":"1","volume-title":"Proc. ACM Manag. Data 1","author":"Doshi Lyric","year":"2023","unstructured":"Lyric Doshi, Vincent Zhuang, Gaurav Jain, Ryan Marcus, Haoyu Huang, Deniz Altinb\u00fcken, Eugene Brevdo, and Campbell Fraser. 2023. Kepler: Robust Learning for Parametric Query Optimization. Proc. ACM Manag. Data 1, 1 (2023), 109:1--109:25."},{"key":"e_1_2_1_12_1","volume-title":"Proceedings of the 2017 ACM International Conference on Management of Data, SIGMOD Conference 2017","author":"Dutt Anshuman","year":"2017","unstructured":"Anshuman Dutt, Vivek R. Narasayya, and Surajit Chaudhuri. 2017. Leveraging Re-costing for Online Optimization of Parameterized Queries with Guarantees. In Proceedings of the 2017 ACM International Conference on Management of Data, SIGMOD Conference 2017, Chicago, IL, USA, May 14-19, 2017. ACM, 1539--1554."},{"key":"e_1_2_1_13_1","doi-asserted-by":"publisher","DOI":"10.5555\/645924.671329"},{"key":"e_1_2_1_14_1","volume-title":"Proceedings of the ACM Web Conference 2023, WWW 2023","author":"Hamedani Masoud Reyhani","year":"2023","unstructured":"Masoud Reyhani Hamedani, Jin-Su Ryu, and Sang-Wook Kim. 2023. GELTOR: A Graph Embedding Method based on Listwise Learning to Rank. In Proceedings of the ACM Web Conference 2023, WWW 2023, Austin, TX, USA, 30 April 2023 - 4 May 2023. ACM, 6--16."},{"key":"e_1_2_1_15_1","doi-asserted-by":"publisher","DOI":"10.14778\/3503585.3503586"},{"key":"e_1_2_1_16_1","volume-title":"Proceedings of 28th International Conference on Very Large Data Bases, VLDB 2002","author":"Hulgeri Arvind","year":"2002","unstructured":"Arvind Hulgeri and S. Sudarshan. 2002. Parametric Query Optimization for Linear and Piecewise Linear Cost Functions. In Proceedings of 28th International Conference on Very Large Data Bases, VLDB 2002, Hong Kong, August 20-23, 2002. Morgan Kaufmann, 167--178."},{"key":"e_1_2_1_17_1","volume-title":"Proceedings of 29th International Conference on Very Large Data Bases, VLDB 2003","author":"Hulgeri Arvind","year":"2003","unstructured":"Arvind Hulgeri and S. Sudarshan. 2003. AniPQO: Almost Non-intrusive Parametric Query Optimization for Nonlinear Cost Functions. In Proceedings of 29th International Conference on Very Large Data Bases, VLDB 2003, Berlin, Germany, September 9-12, 2003. Morgan Kaufmann, 766--777."},{"key":"e_1_2_1_18_1","doi-asserted-by":"crossref","first-page":"132","DOI":"10.1007\/s007780050037","article-title":"Parametric Query Optimization","volume":"6","author":"Ioannidis Yannis E.","year":"1997","unstructured":"Yannis E. Ioannidis, Raymond T. Ng, Kyuseok Shim, and Timos K. Sellis. 1997. Parametric Query Optimization. VLDB J. 6, 2 (1997), 132--151.","journal-title":"VLDB J."},{"key":"e_1_2_1_19_1","volume-title":"WWW '21: The Web Conference 2021","author":"Jia Yiling","year":"2021","unstructured":"Yiling Jia, Huazheng Wang, Stephen Guo, and Hongning Wang. 2021. PairRank: Online Pairwise Learning to Rank by Divide-and-Conquer. In WWW '21: The Web Conference 2021, Virtual Event \/ Ljubljana, Slovenia, April 19-23, 2021. ACM \/ IW3C2, 146--157."},{"key":"e_1_2_1_20_1","volume-title":"Seo, Wook-Shin Han, Kangwoo Choi, and Jaehyok Chong. 2022. Learned Cardinality Estimation: An In-depth Study. In SIGMOD '22: International Conference on Management of Data","author":"Kim Kyoungmin","year":"2022","unstructured":"Kyoungmin Kim, Jisung Jung, In Seo, Wook-Shin Han, Kangwoo Choi, and Jaehyok Chong. 2022. Learned Cardinality Estimation: An In-depth Study. In SIGMOD '22: International Conference on Management of Data, Philadelphia, PA, USA, June 12 - 17, 2022. ACM, 1214--1227."},{"key":"e_1_2_1_21_1","first-page":"86","article-title":"A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration. Data Sci","volume":"6","author":"Lan Hai","year":"2021","unstructured":"Hai Lan, Zhifeng Bao, and Yuwei Peng. 2021. A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration. Data Sci. Eng. 6, 1 (2021), 86--101.","journal-title":"Eng."},{"key":"e_1_2_1_22_1","doi-asserted-by":"publisher","DOI":"10.14778\/2850583.2850594"},{"key":"e_1_2_1_23_1","volume-title":"Learning to Rank for Information Retrieval and Natural Language Processing","author":"Hang Li.","unstructured":"Hang Li. 2014. Learning to Rank for Information Retrieval and Natural Language Processing, Second Edition. Morgan & Claypool Publishers."},{"key":"e_1_2_1_24_1","volume-title":"Bao: Making Learned Query Optimization Practical. In SIGMOD '21: International Conference on Management of Data","author":"Marcus Ryan","year":"2021","unstructured":"Ryan Marcus, Parimarjan Negi, Hongzi Mao, Nesime Tatbul, Mohammad Alizadeh, and Tim Kraska. 2021. Bao: Making Learned Query Optimization Practical. In SIGMOD '21: International Conference on Management of Data, Virtual Event, China, June 20-25, 2021. ACM, 1275--1288."},{"key":"e_1_2_1_25_1","doi-asserted-by":"publisher","DOI":"10.14778\/3342263.3342644"},{"key":"e_1_2_1_26_1","doi-asserted-by":"publisher","DOI":"10.14778\/3342263.3342644"},{"key":"e_1_2_1_27_1","first-page":"4","volume-title":"Proc. ACM Manag. Data 1","author":"Mo Songsong","year":"2023","unstructured":"Songsong Mo, Yile Chen, Hao Wang, Gao Cong, and Zhifeng Bao. 2023. Lemo: A Cache-Enhanced Learned Optimizer for Concurrent Queries. Proc. ACM Manag. Data 1, 4 (2023), 247:1--247:26."},{"key":"e_1_2_1_28_1","doi-asserted-by":"publisher","DOI":"10.1609\/aaai.v30i1.10139"},{"key":"e_1_2_1_29_1","volume-title":"Proceedings of the 31st International Conference on Very Large Data Bases","author":"Reddy Naveen","year":"2005","unstructured":"Naveen Reddy and Jayant R. Haritsa. 2005. Analyzing Plan Diagrams of Database Query Optimizers. In Proceedings of the 31st International Conference on Very Large Data Bases, Trondheim, Norway, August 30 - September 2, 2005. ACM, 1228--1240."},{"key":"e_1_2_1_30_1","doi-asserted-by":"publisher","DOI":"10.14778\/3636218.3636229"},{"key":"e_1_2_1_31_1","volume-title":"echnical Report].","author":"Songsong Mo","year":"2024","unstructured":"Mo Songsong, Zhao Yue, Bao Zhifeng, Xu Quanqing, Yang Chuanhui, and Cong Gao. 2024. RankPQO: Learning-to-Rank for Parametric Query Optimization [Technical Report]. (2024), 1--15. https:\/\/github.com\/songsong945\/RankPQO\/blob\/main\/RankPQO_Technical_Report.pdf"},{"key":"e_1_2_1_32_1","doi-asserted-by":"publisher","DOI":"10.14778\/3368289.3368296"},{"key":"e_1_2_1_33_1","doi-asserted-by":"publisher","DOI":"10.14778\/3485450.3485459"},{"key":"e_1_2_1_34_1","doi-asserted-by":"publisher","DOI":"10.14778\/3494124.3494126"},{"key":"e_1_2_1_35_1","doi-asserted-by":"publisher","DOI":"10.1145\/3514221.3517885"},{"key":"e_1_2_1_36_1","doi-asserted-by":"publisher","DOI":"10.14778\/3554821.3554830"},{"key":"e_1_2_1_37_1","doi-asserted-by":"publisher","DOI":"10.14778\/3565838.3565846"},{"key":"e_1_2_1_38_1","volume-title":"Reinforcement Learning with Tree-LSTM for Join Order Selection. In 2020 IEEE 36th International Conference on Data Engineering (ICDE). 1297--1308","author":"Yu Xiang","year":"2020","unstructured":"Xiang Yu, Guoliang Li, Chengliang Chai, and Nan Tang. 2020. Reinforcement Learning with Tree-LSTM for Join Order Selection. In 2020 IEEE 36th International Conference on Data Engineering (ICDE). 1297--1308."},{"key":"e_1_2_1_39_1","doi-asserted-by":"publisher","DOI":"10.14778\/3529337.3529349"},{"key":"e_1_2_1_40_1","doi-asserted-by":"publisher","DOI":"10.1007\/s11280-020-00846-3"},{"key":"e_1_2_1_41_1","doi-asserted-by":"publisher","DOI":"10.14778\/3583140.3583160"}],"container-title":["Proceedings of the VLDB Endowment"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.14778\/3712221.3712248","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2025,4,7]],"date-time":"2025-04-07T18:27:58Z","timestamp":1744050478000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.14778\/3712221.3712248"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2024,11]]},"references-count":41,"journal-issue":{"issue":"3","published-print":{"date-parts":[[2024,11]]}},"alternative-id":["10.14778\/3712221.3712248"],"URL":"https:\/\/doi.org\/10.14778\/3712221.3712248","relation":{},"ISSN":["2150-8097"],"issn-type":[{"value":"2150-8097","type":"print"}],"subject":[],"published":{"date-parts":[[2024,11]]},"assertion":[{"value":"2025-04-07","order":3,"name":"published","label":"Published","group":{"name":"publication_history","label":"Publication History"}}]}}