COOOL: A Learning-To-Rank Approach for SQL Hint Recommendations

TL;DR

COOOL introduces a learning-to-rank method for SQL query hint recommendations, improving query plan selection efficiency and practicality over traditional regression-based approaches, with extensive validation on PostgreSQL.

Contribution

The paper proposes COOOL, a novel learning-to-rank approach for SQL hint recommendations that overcomes regression-based limitations and supports multi-dataset applications.

Findings

COOOL outperforms PostgreSQL and state-of-the-art methods.

It effectively distinguishes query plans with different latencies.

The approach is validated on join-order-benchmark and TPC-H datasets.

Abstract

Query optimization is a pivotal part of every database management system (DBMS) since it determines the efficiency of query execution. Numerous works have introduced Machine Learning (ML) techniques to cost modeling, cardinality estimation, and end-to-end learned optimizer, but few of them are proven practical due to long training time, lack of interpretability, and integration cost. A recent study provides a practical method to optimize queries by recommending per-query hints but it suffers from two inherited problems. First, it follows the regression framework to predict the absolute latency of each query plan, which is very challenging because the latencies of query plans for a certain query may span multiple orders of magnitude. Second, it requires training a model for each dataset, which restricts the application of the trained models in practice. In this paper, we propose COOOL to predict Cost Orders of query plans to cOOperate with DBMS by Learning-To-Rank. Instead of estimating absolute costs, COOOL uses ranking-based approaches to compute relative ranking scores of the costs of query plans. We show that COOOL is theoretically valid to distinguish query plans with different latencies. We implement COOOL on PostgreSQL, and extensive experiments on join-order-benchmark and TPC-H data demonstrate that COOOL outperforms PostgreSQL and state-of-the-art methods on single-dataset tasks as well as a unified model for multiple-dataset tasks. Our experiments also shed some light on why COOOL outperforms regression approaches from the representation learning perspective, which may guide future research.