⏱️ Lectura: 12 min
A language model with just 4 billion parameters managed to generate query plans 44.7% faster than Postgres’s default output, according to an experiment documented by engineer Rohan Bansal. The finding arrives ten years after an academic study confirmed that database optimizers, despite decades of research, still make decisions far from optimal.
📑 En este artículo
The experiment, published by Bansal on his personal blog, combines supervised fine-tuning (SFT) and reinforcement learning (RL) on a Qwen model so it learns to suggest execution hints to Postgres before the engine builds its own plan.
TL;DR
- A 4B Qwen model, fine-tuned with SFT and RL, cut latency by 44.7% across 113 multi-join queries compared to Postgres’s default plan.
- Before training, the model failed to produce a valid plan for 99 of those 113 queries.
- The measurement rig used a rented node with 2x H100 GPUs for vLLM and the trainer, plus 4 Postgres containers on a local desktop.
- The author designed his own variant of GRPO to deal with the noise in measuring real execution times.
- Off-policy distillation was applied to about 500 trajectories generated by an agent based on GPT-6 Astra.
- The 2015 study by Leis et al., repeated 10 years later, confirmed that database optimizers still fail at join ordering.
- The system uses the pg_hint_plan extension to inject hints like HashJoin, NestLoop, IndexScan, and Leading without touching the Postgres engine.
Introduction
When Postgres receives a query with multiple JOINs, it has to decide in what order to combine the tables and with which algorithm (hash join, nested loop, or merge join). That decision, made by the query planner, determines whether the query takes 70 milliseconds or 700. The planner estimates costs using internal statistics, but those estimates degrade quickly when there are correlations between columns or uneven distributions.
The question Bansal asked himself is direct: if generating a good query plan is hard, but verifying whether a plan is fast or slow is trivial (just run it and measure the time), why not let a language model learn to propose query plans through trial and error, the same way it learns to play a game with a clear reward function?
What happened: how the query plans were trained
The training process works through rollouts. For the same SQL query, the Qwen model generates four distinct candidate query plans in the form of hints. Each candidate is sent to a real Postgres instance, which executes it and measures its latency against the engine’s default plan. That time difference becomes a scalar reward that is propagated backward to adjust the model’s weights.
An example from the experiment itself illustrates the mechanism. For the query SELECT count(*) FROM title t JOIN movie_companies mc ON mc.movie_id = t.id JOIN company_name cn ON cn.id = mc.company_id WHERE cn.name = 'Toho', Postgres’s default plan took 118 ms. The hint /*+ Leading((cn mc) t) */ brought it down to 74 ms, while /*+ NestLoop(t mc) */ made it worse at 163 ms. That variance between hints, measured rollout by rollout, is exactly the signal the RL algorithm uses to learn which join structures work best in which context.
sequenceDiagram
participant Q as Qwen4B
participant P as Postgres
participant R as Reward
Q->>P: proposes plan hint
P-->>Q: measures execution time
Q->>R: rollout with latency
R-->>Q: updates weights via GRPO
Note over Q,P: 4 rollouts repeated per query💡 Tip: Postgres, unlike Oracle or SQL Server, doesn’t accept native hints in standard SQL. This entire mechanism depends on the external extension pg_hint_plan, which interprets special comments like /*+ HashJoin(a b) */ before the planner builds the actual plan.
Context and history: why optimizers keep failing
The underlying question (how good query optimizers really are) isn’t new. A group of researchers led by Viktor Leis formally raised it in 2015 and repeated it a decade later, finding that, despite ten additional years of research in industry and academia, commercial and open source optimizers still produce plans far from optimal in queries with multiple joins.
The technical reason has a name: join ordering is an NP-hard problem. With just ten tables in a query, the number of possible join trees grows combinatorially, and no optimizer can afford to explore all of them within the time a user is willing to wait for a plan. That’s why engines like Postgres rely on heuristics and statistical estimates that, when they fail, produce query plans far below what’s possible.
That same problem is, paradoxically, good news for reinforcement learning. Generating an optimal plan is hard, but verifying whether one plan is better than another is as simple as timing two runs. When there’s a single optimization axis, in this case execution time, the problem reduces to reinforcing the behaviors that produce faster query plans, without needing a human to manually label which plan is correct.
Technical details and performance
The experiment uses a portion of the IMDb dataset, with tables like title (around 1 million rows), movie_companies (about 2 million rows, a junction table between movies and companies), and company_name (nearly 100,000 rows). It’s a classic join-benchmarking schema because it combines large tables, junction tables, and small catalog tables, forcing the optimizer to choose between multiple reasonable strategies.
-- Simplified schema used in the experiment (IMDb)
CREATE TABLE title (
id integer PRIMARY KEY,
title text,
production_year integer,
kind_id integer
);
CREATE TABLE movie_companies (
id integer PRIMARY KEY,
movie_id integer, -- FK -> title.id
company_id integer, -- FK -> company_name.id
company_type_id integer,
note text
);
CREATE TABLE company_name (
id integer PRIMARY KEY,
name text,
country_code text
);To keep operating system noise from contaminating the reward signal, the author had to solve an infrastructure problem rarely discussed in RL papers: Linux page cache contention between concurrent containers. If two Postgres containers share cache memory unevenly, the same plan can measure different times across runs, confusing the training algorithm. The final rig separated training (a rented node with 2x H100 GPUs running vLLM and the training process) from measurement (4 Postgres containers running on a local desktop), and designed a custom GRPO (Group Relative Policy Optimization) variant specifically built to normalize rewards in a noisy measurement environment.
Beyond pure RL, the model went through an off-policy distillation stage: around 500 trajectories generated by an agent based on GPT-6 Astra served as behavioral examples before reinforcement learning fine-tuned the policy. This combination (SFT with trajectories from a larger model, followed by RL with verifiable rewards) is the same pattern used today to train mathematical reasoning or code generation models, applied here to a very different domain: query plans for relational databases.
| Hint (pg_hint_plan) | When to use it | Advantage | Limitation |
|---|---|---|---|
| HashJoin(a b) | Large tables without a useful index on the join condition | Good performance if the hash fits in memory | Consumes work_mem, may spill to disk if the hash is large |
| NestLoop(a b) | One of the tables is small or already heavily filtered | Low overhead with few rows | Degrades badly if the row estimate is wrong |
| IndexScan(a) | A selective index exists on the filtered column | Avoids reading the entire table | Counterproductive if selectivity is low |
| Leading((a b) c) | The optimal order among three or more tables is known | Eliminates the optimizer’s combinatorial search | Needs revisiting if schema or data changes |
⚠️ Heads up: Join ordering is an NP-hard problem: there’s no guarantee that the model, or Postgres, will find the global optimum, only a plan better than the default across the evaluated set of queries. The author himself reports the result on 113 specific queries from the IMDb dataset, not as a universal guarantee for any workload.
How to try it
You don’t need to reproduce the reinforcement learning training to experiment with the core idea: force alternative query plans in Postgres and measure the difference yourself with pg_hint_plan.
Linux (Debian / Ubuntu)
# Install Postgres and the development headers
sudo apt install postgresql postgresql-server-dev-16
git clone https://github.com/ossc-db/pg_hint_plan.git
cd pg_hint_plan && make && sudo make installmacOS (Homebrew)
# Install Postgres with Homebrew
brew install postgresql@16
git clone https://github.com/ossc-db/pg_hint_plan.git
cd pg_hint_plan && make USE_PGXS=1 && make USE_PGXS=1 installWindows (PowerShell)
# Requires PostgreSQL installed and Visual Studio Build Tools
git clone https://github.com/ossc-db/pg_hint_plan.git
cd pg_hint_plan
nmake /f win32.mak
nmake /f win32.mak installWith the extension installed, the first step is to see which plan Postgres chooses on its own:
-- Enable the extension in the database
CREATE EXTENSION pg_hint_plan;
-- View the default plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM title t
JOIN movie_companies mc ON mc.movie_id = t.id
JOIN company_name cn ON cn.id = mc.company_id
WHERE cn.name = 'Toho';That command shows the actual chosen plan and the measured execution time. The next step is to force a different hint and compare, exactly like each training rollout did:
-- Verify that the extension is active and logs the applied hints
SET pg_hint_plan.debug_print = 'on';
/*+ HashJoin(mc cn) */
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM title t
JOIN movie_companies mc ON mc.movie_id = t.id
JOIN company_name cn ON cn.id = mc.company_id
WHERE cn.name = 'Toho';
-- A line should appear in the Postgres log
-- "pg_hint_plan: hint syntax OK" confirming the hint was appliedThe log line with pg_hint_plan: hint syntax OK is how you verify that the hint was actually applied and not ignored due to a syntax error, something easy to overlook the first time you use the extension.
Impact and analysis
The most striking result of the experiment isn’t just the 44.7% reduction in latency, but the starting point: before training, the 4B Qwen model couldn’t even produce a syntactically valid plan for 99 of the 113 queries evaluated. That means most of the gain didn’t come from fine-tuning a model that already understood the domain, but from teaching it, from scratch, a structured task it didn’t master at all.
For an engineering team, this doesn’t mean replacing Postgres’s optimizer tomorrow with a 4B model running in production. Every query would require an inference call before executing, adding latency and compute cost that only makes sense for heavy analytical queries, not millisecond OLTP transactions where the model’s own overhead would exceed any savings. The experiment’s own design (measuring against 113 queries from a specific IMDb benchmark) also doesn’t guarantee the model generalizes to different schemas and data distributions without retraining.
💭 Key point: The experiment’s GRPO variant exists because measuring the real time of a query in Postgres varies between runs due to Linux page cache. Without normalizing that noise, the RL algorithm would end up reinforcing plans that seem fast by chance, not by design.
What is clearly demonstrated is the general pattern: when a task has a verifiable and cheap-to-compute reward function (in this case, timing a SQL query), a relatively small model can outperform a mature heuristic system with decades of engineering behind it, as long as it’s given enough reinforcement signal. It’s the same principle behind mathematical reasoning models trained with RL, applied here to a systems problem.
What’s next
Bansal himself frames the experiment as a proof of concept, not a finished product. Logical next steps include expanding the benchmark beyond the 113 IMDb queries, measuring the model’s inference cost against the actual execution time savings to know at what point it stops being worth it, and exploring whether the same verifiable-reward RL approach works for other optimizer decisions, such as index selection or per-query work_mem configuration.
📖 Summary on Telegram: View summary
Try it yourself: install pg_hint_plan on a test database with IMDb data and compare the default plan against a manual hint using EXPLAIN (ANALYZE, BUFFERS) to see the difference with your own eyes.
Frequently Asked Questions
Does this model replace the Postgres optimizer?
No. The experiment proposes external hints via pg_hint_plan; Postgres remains the engine that executes the query and decides the low-level details of each operator.
Why use a model with only 4B parameters instead of a larger one?
Because the task (choosing among a bounded set of structured hints) doesn’t require the general knowledge of a large model, and a small model is cheaper to run per query if the goal is production use.
What is GRPO?
Group Relative Policy Optimization is a reinforcement learning technique that compares several rollouts generated for the same input against each other, instead of against a separate value model. The author built his own variant to tolerate Postgres’s measurement noise.
Why is join ordering so difficult?
Because it’s an NP-hard problem: the number of possible ways to combine several tables grows combinatorially, and no optimizer can evaluate them all in reasonable time.
Do I need to modify the Postgres source code to try this?
No. Installing the pg_hint_plan extension is enough; it loads like any other Postgres extension without recompiling the engine.
Where can I see the code and full details of the experiment?
Rohan Bansal’s original article, with the full details of the training rig and results, is available on his personal blog.
References
- rohanbansal.com/qorl: original article with the full details of the experiment, the training rig, and the results.
- github.com/ossc-db/pg_hint_plan: official repository of the extension used to inject hints into Postgres.
- postgresql.org/docs/current/using-explain.html: official EXPLAIN documentation, used to verify query plans.
- arxiv.org/abs/2402.03300: paper introducing GRPO, the RL technique the experiment’s custom variant was based on.
📱 Enjoying this content? Follow @programacion on Telegram for daily tech content in Spanish: quick summaries, fresh content every day.
Imagen destacada: Foto de Branko Stancevic en Unsplash
0 Comments