Training a 4B model to produce 81% faster query plans than Postgres

Four reinforcement learning rollouts were conducted for a single query. Qwen generated a candidate strategy per rollout and sent it to Postgres for evaluation against its default plan. Scalar rewards were assigned to each rollout, which backpropagated to update Qwen's weights. This research question was initially posed by Leis et al. in 2015 and revisited 10 years later.

Despite extensive research, query optimizers have yet to fully improve. The idea of a Postgres database knowing all aspects of its tables seemed straightforward until the complexity of query optimization was realized. Join ordering, a critical component of query optimization, is known to be NP-hard, making query optimizers inherently challenging.

However, verifying whether a query plan is good or bad is relatively easy. Language models excel at learning tasks with easily verifiable outputs, making them ideal for optimizing query plans. The experiment aimed to determine if a small, open-weights model could be post-trained via supervised fine-tuning (SFT) and agentic reinforcement learning (RL) to produce Postgres query plans that outperform Postgres's default plans.

The results were overwhelmingly positive. The experiment considered a slice of the IMDb dataset to answer the question: "Which Japanese companies produced the most titles in the 2000s?" A standard query was executed, and the path Postgres took to retrieve the data was analyzed. Selective predicates, such as filtering conditions in a WHERE clause, significantly impact the join order.

The study discovered that there are eight valid join trees, and considering commutativity, there are 4,608 different ways to run the query. Despite the vast number of possible join orders, Postgres does not evaluate all of them due to its reliance on dynamic programming and a genetic algorithm to prune the search space. However, Postgres estimates cardinalities using statistics from the pg_statistic table, applying uniform distribution assumptions that can lead to significant inaccuracies.

This research demonstrates that a small, open-weights model can be effectively trained to optimize query plans, outperforming Postgres's default plans.