RankPQO: Learning-to-Rank for Parametric Query Optimization

Download PDF

Abstract

Parametric Query Optimization (PQO) is crucial for efficiently han-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 perfor-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 mi-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 ac-showcasing significant improvements in both efficiency and accuracy, as compared to baselines. Specifically, it accelerates the PostgreSQL optimizer by up to 2.57 × and surpasses the best exist-PostgreSQL optimizer by up to 2.57 × and surpasses the best existing baseline by up to 1.36 × .