Can Large Language Models Be Query Optimizer for Relational Databases?

Jie Tan, Kangfei Zhao, Rui Li, Jeffrey Xu Yu, Chengzhi Piao, Hong Cheng, Helen Meng, Deli Zhao, Yu Rong

Introduction

Query optimization serves as the "brain" of modern database management systems (DBMS), tasked with identifying the most efficient execution plan for a given SQL query. Traditionally, this process involves enumerating a vast search space of potential plans and selecting the one with the lowest estimated cost. However, traditional cost models are often brittle, relying on simplified heuristics that frequently lead to "cardinality misestimation"—incorrectly predicting the number of rows a join will produce. This often results in the selection of suboptimal, slow-running plans.

While learned optimizers have attempted to address these issues using machine learning, they often remain constrained by the search space of the underlying traditional optimizer. This research introduces LLM-QO, a framework that reimagines query optimization as a sequence generation task, leveraging the reasoning capabilities of Large Language Models (LLMs) to generate optimal execution plans directly.

Methodology: The LLM-QO Framework

LLM-QO shifts the paradigm from "enumerate and search" to direct autoregressive plan generation. The framework consists of three primary components: data preparation via QInstruct, a two-stage fine-tuning process, and structured output generation.

QInstruct and Prompt Design

To enable the LLM to "reason" about database plans, the researchers developed QInstruct. This specialized prompt format provides the model with more than just the SQL query; it includes auxiliary information such as table statistics (minimum/maximum values and distinct counts) and a planning demonstration (a one-shot example). Providing these statistics is crucial, as the model requires data distribution context to make logical join decisions.

Two-Stage Fine-Tuning

The model undergoes a rigorous two-stage training process to ensure both validity and performance:

  1. Query Instruction Tuning (Qit): The base model is first trained to generate valid execution plans by imitating expert optimizers like PostgreSQL and Oracle.
  2. Query Direct Preference Optimization (Qdpo): Using the Direct Preference Optimization (DPO) algorithm, the model learns to distinguish between "good" and "ordinary" plans. By comparing plans based on their actual execution times, the model "distills" the best traits of multiple optimizers and learns to prefer the fastest execution paths.
The LLM-QO framework workflow, illustrating QInstruct preparation, the two-stage fine-tuning process (Qit and Qdpo), and the inference pipeline.

Structured Output and Reasoning

To mitigate the risk of "hallucinations" (e.g., joining non-existent tables), LLM-QO outputs a "planning path"—a step-by-step reasoning of the join order—followed by a final "bracket sequence" that can be directly executed by the database engine.

Experimental Evaluation

The framework was evaluated using three major workloads: the real-world IMDB dataset, the JOB-light join order benchmark, and the complex DSB (Decision Support Benchmark). Training data was distilled from the execution plans of PostgreSQL, Oracle, and IBM DB2.

Key Results and Insights

LLM-QO demonstrated significant performance improvements over traditional and learned baselines. Notably, it achieved a 68.7% reduction in average execution time on the DSB workload compared to the PostgreSQL built-in optimizer.

Comparison of execution times across various query templates, showing LLM-QO's performance relative to PostgreSQL and Oracle.

Beyond raw speed, the research yielded several key insights:

  • The Necessity of Statistics: Removing database statistics from the prompt led to a significant performance degradation, confirming that LLMs require data context to optimize effectively.
  • Generalization Capabilities: LLM-QO showed a surprising ability to handle Out-of-Distribution (OOD) queries, including templates and join structures not encountered during training.
  • Efficiency: While LLM inference is computationally heavier than traditional cost models, the researchers showed that batching on modern GPUs can reduce plan generation time to sub-second levels, suggesting potential for real-world deployment.

Conclusion and Future Work

LLM-QO proves that LLMs can effectively serve as database query optimizers by leveraging their reasoning and sequence generation capabilities. While the approach currently focuses on Selection-Project-Join (SPJ) queries and faces some latency challenges compared to C++-based cost models, it represents a significant step toward more robust, learned database internals. Future work will explore supporting more complex query types (e.g., aggregations and subqueries) and adapting to dynamic data distributions.

Summary updated

BibTeX
@inproceedings{tan2025can,
  author = {Jie Tan and Kangfei Zhao and Rui Li and Jeffrey Xu Yu and Chengzhi Piao and Hong Cheng and Helen Meng and Deli Zhao and Yu Rong},
  title = {{Can Large Language Models Be Query Optimizer for Relational Databases?}},
  booktitle = {Proc. ACM Manag. Data},
  year = {2025},
  url = {https://openreview.net/forum?id=vXrWj5OGN6},
}
Figure