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

“81% faster query plans than Postgres”…on an 8 GB dataset that fits entirely in memory, with shared_buffers constrained to a fraction of that, queries warmed before measuring, and read-only SELECTs.

I would be cautious about over fitting, it’s tough to say if those query plans would really be more optimal than Postgres heuristics at scale and with a bit more realistic OLTP workloads.

In any case, such is life with profile guided optimization. Many of us appreciate how database workloads can drift over time and with scale.

Kudos to the author for getting their hands dirty and writing up their experiments.

I am skeptical of these results given that the end has:

> Favorite settings The model regularly used enable_sort=off and random_page_cost=1.1

If random_page_cost wasn't set correctly for the default cases postgres's query planner can generate terrible plans (unless you're running on a spinning disk).

That could easily explain the difference by itself.

Engineer: "HELP, our production DB is frozen on this query that worked fine before!"

Infra: "Hmm, let's check... Well would you look at that, it seems like your LLM query planner usually works and produces fast queries, but this time when you changed a variable name to trigger query rebuild, it happened to hallucinate and miss an index, would you mind re-running the LLM a few times until you get a faster query?"

Optimal plan construction is math-heavy, algorithm-heavy and vary even by workload. There are options like creating just-in-time indexes, so solution space grows even faster than article presents. Sometimes it is the query planner which is the slow part of total execution time.

LLM is kind of blunt weapon to use here. I am waiting rather for alphago style neural net heuristic.

> Frontier intelligence is extremely powerful; the distillation I did off Astra trajectories is proof enough that large models are not going anywhere

Wouldn't admitting this invite trouble due to accusations of distillation flying around between closed and open models.

Unless they’ve been elided, there were no indices other than the PK on any table, and no additional statistics. There are correlated columns here: a given country may have produced more movies in a given range of years, as its movie industry built up; a given country may produce more TV series than movies, etc.

Nearly every time I’ve seen someone resorting to hints for a query, it’s because their statistics are incorrect. Adding hints is papering over the problem, and can backfire later if the data shape changes.

> I paid ~$800 to rent a 2x H100 SXM node from Lambda for ~95 hours, and ~$400 in OpenAI API fees to generate the Astra trajectory demonstrations.

> a tiny 4B model went from not being able to understand the harness it was wrapped in, to achieving a 1.81x geometric mean speedup and a summed latency decrease of 44.7% across a workload of join-heavy SQL queries

I can’t find it in the article (may have skimmed it too much), but I suspect they didn’t include those ~95 hours in the benchmark numbers.

I think all database vendors know their query optimizers could do much better if they could afford to spend lots of time to derive query plans.

⇒ this may be useful for some workloads, but even then, can you afford to spend hours every now and then to update your 4B model to ensure it still picks a good query plan?

This was a thoroughly enjoyable read, both the writing and presentation. I really liked the level of writing as it's basically introducing a whole lot of advanced topics but at just the right level for a non-AI researcher type of engineer like myself to be able to understand what's going on, and I felt it made some elements of LLMs actually something I could understand rather than wizardry done by maths PhDs. Probably because it's more like applied engineering rather than hard mathematics here. Thank you for a delightful post.

Two things. One is that to remind folks that PostgreSQL has used a tiny form of "artificial intelligence", that is GEQO - Genetic Query Optimizer, since 2001.

Second. How would that LLM-based query optimizer work in a real-world 10,000 qps ERP system with very large shape of queries? I'm not saying it's useless, it just won't replace a real query planner soon. Latencies would skyrocket.

I have a theory that soon enough every code library will ship a CLI and a very tiny finetune for that specific lib alongside it

81%is nothing. It is not hard to be more than 3x better than Postgres [1]. And you don’t need a model to do that, let alone a 4B model. How much additional compute is needed to just run that model?

[1] https://github.com/datalevin/datalevin/tree/master/benchmark...

It's certainly a good idea to train a neural network to find good query plans but... an LLM??

An 8GB dataset? That’s literally a few seconds worth of records generated in my world, and any speedup at that scale is completely meaningless.

Let’s talk when you are looking at double digit TB at a minimum.

Funny enough I was thinking about something very similar to this based on the Jev model posted yesterday.

> How hard can it be?

> As it turns out: enormously hard.

This exactly tracks me learning everything

Suppose you run a platform, and you run a couple thousand different queries of different types throughout the day. It would make sense to have an auto-optimizer that would read long queries, ponder over them with an LLM, come up with some good plans, and store them as hints. This seems like quite a good idea? Is there a product for this?

such a (visually) beautiful blogpost.

Mentioned elsewhere but classic ml seems the right tool for this problem

Really creative use of distillation here.

Nice article about how to train/fine-tune a language model.

However, it misses the whole point of database query planning. You can't just ignore the planning time itself, as if the database query were a static entity to be optimized once at a leisurely pace.

The real constraint on live query planners is quite different: they must improve the combined time - planning + query - based on live database statistics. You can amortize the planning with prepared statements, but that too is fraught since optimal plans can change quite frequently and based on input parameters. "Live" and "faster than the queries themselves" are the hard requirements to be considered a viable database query planner. This project does neither.

Aren't optimizations suppose to be deterministic?

But how will you know that the query plan actually does what your query asked for?

[flagged]

[flagged]

[flagged]

[dead]

[dead]

[dead]

[dead]

TL:DR; for people. Index your data properly.

why is this write-up so long?

Need 5 days just to go through it.