“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.

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.

The end game is adaptive query plans.

A big reason the initial plan isn't guaranteed to be optimal, even with all the right indexes, is that table statistics aren't perfect. For example, you might track a column's correlation (how closely the column's logical ordering matches its physical ordering in the heap), but that won't be broken down at a per value level. Postal code X might be very correlated, while postal code Y that is used in your query is completely uncorrelated.

The ideal solution is to pick one plan initially, and then update a temporary query-specific statistic model based on the data you actually read while executing the query. Then periodically re-evaluate if an alternative plan would be faster, switching to it in a way that doesn't throw away the current partial result.

Of course switching plans mid flight is very complicated, but Oracle and SQL server both support this feature, so hopefully it lands in Postgres at some point.

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.

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.

> The goal isn’t to try and beat Postgres on the time/efficiency Pareto frontier for one-off queries, but we may be able to beat it on queries that run over and over again.

This is the most important part. Most queries are either quick transactions that can run thousands of times per second, or complex but predictable scheduled analytics.

One off queries are pretty rare and optimizing for them instead of the common ones is a massive own goal almost every database is repeating. I don't think you need a LLM to beat Postgres.

undefined

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.

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...

Neat. Can this model learn which table indexes to create that would improve performance? In my experience, nested loops will be chosen in a query plan over poorly chosen indexes, but well chosen indexes will result in drastic speed ups.

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

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

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.

> How hard can it be?

> As it turns out: enormously hard.

This exactly tracks me learning everything

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.

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

such a (visually) beautiful blogpost.

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?

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

Aren't optimizations suppose to be deterministic?

Really creative use of distillation here.

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

[flagged]

[flagged]

[flagged]

[flagged]

[flagged]

[flagged]

[dead]

[dead]

[dead]

[dead]

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

[dead]

why is this write-up so long?

Need 5 days just to go through it.