GoKawiilHow good are query optimizers, really?
Leis et al. asked this exact question in 2015. Then, they asked it again 10 years later.
Despite an enormous body of research spanning a decade since their original exploration, they found that query optimizers continue to leave much to be desired.
I was surprised when I first learned about this. A Postgres database should know everything about the stuff that lives in its tables, no? How hard can it be?
As it turns out: enormously hard. In fact, one particular task a query optimizer needs to do, join ordering, is known to be NP-hard.
So query optimizers are hard. What’s not as hard is verifying whether a query plan an optimizer picks is good or not. Put simply, a good query optimizer produces plans that run fast, and a bad one produces slow plans. Language models are particularly good at learning how to do tasks with easily verifiable outputs. Because there’s a single axis to optimize for—execution time of a query—the problem beautifully reduces to reinforcing the behaviors that guide a model to produce faster query plans.
What follows is a breakdown of an experiment I ran to explore the question: can a small, open-weights model be post-trained via supervised fine-tuning (SFT) and agentic reinforcement learning (RL) to produce Postgres query plans that beat Postgres’s default plans?
The answer to our question is a resounding yes. Highlights include:
Attaining a 44.7% latency reduction across 113 join-heavy queries from a 4B model initially unable to produce a query plan for 99 of them
across 113 join-heavy queries from a 4B model initially unable to produce a query plan for 99 of them Constructing a Postgres measurement rig that minimizes Linux page cache contention noise across concurrent containers
... continue reading