“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.
By refibrillator 4 hours ago
With a 4B parameter model that probably ran through 8GBs of RAM multiple times to run.
At a certain point we should seriously talk about CUDA accelerating Postgres instead.
By dragontamer 3 hours ago
I would think it's possible to make it so that the 4B model only needs to be called during an initial phase, and then the same queries it constructed can just be re-used with values replaced, unless you're generating a lot of unique on-the-fly query shapes.
By soerxpso 3 hours ago
With query hints finally being added it’d probably be doable as an extension
By setr an hour ago
pardon but aren't disks usually the bottleneck? im all for CUDA acceleration and CUDA accelerating culture
By bt1a 2 hours ago
Parent post was talking about an 8GB dataset.
8GB isn't even CPU RAM these days. That's GPU super-mega-awesome ram. Ordinary Server CPUs are regularly pushing 2TB capacities.
GPUs are in the 8GB to 32GB typically, at least for smaller and more regular GPUs. This GPU RAM is also well known to be at least 10x the bandwidth of CPU RAM.
By dragontamer 31 minutes ago
I remember projects like PG-Strom back in the day, very cool stuff
By voganmother42 an hour ago
Which is why a good query plan is so important, so that as much disk I/O can be avoided as possible (predicate push down, index elimination, join ordering, partition/scan pruning). Like the old CTE optimization fence problem.
By tomnipotent an hour ago
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.
By happyopossum 7 minutes ago
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.
By sgarland an hour ago
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?"
By 2001zhaozhao 3 hours ago
Funnily enough, you could replace "LLM query planner" with just "query planner" and this comment would still hold true
By malisper 2 hours ago
I’ve seen this happen to SQL Server many times. Every time the solution is a stored proc with the recompile option enabled.
By reval 44 minutes ago
Our database 12.34 was working great, but 12.35 deployment had some optimizer changes that had regression on exact scenario that you have in your statistics. Shit happens, sorry. Use this hint.
By galkk 2 hours ago
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.
By hamilyon2 4 hours ago
I also wondered why an LLM would be the right starting point. Why would Balzac or billions of lines of rwir code or reddit be relevant to mapping this smallish, well-defined language (SQL) to this other tiny constrained specification language (the query plan suggestions)? You could make a (relatively) tiny network and then actually pass it some relevant features of the actual data, like as numbers, not just as text returned from a tool call.
By topaz0 2 hours ago
It’s ultimately based on a lot of hand-written heuristics. Google has some non-LLM based machine learning technique to guide optimization heuristics in LLVM; that would be closer to what you are looking for.
By kccqzy 2 hours ago
What if we use a hybrid model of using both query optimizer and LLM?
Whichever produces better result, the database can use?
- a question from someone with lack of DB depth, me.
The immediate problem: How do you know which one is better without running them?
By Sesse__ 3 hours ago
You create formulas to estimate the cost of running a given query plan. Use statistics collected about the tables (e.g. how many rows) to try to be accurate. The topic is "Cost Based Optimization".
By haroldl 41 minutes ago
Could you A/B at random, use that to collect data and eventually feed that back in to prefer A or B depending on the shape of the query?
By HighlandSpring 3 hours ago
Not someone familiar with DB internals, but could you run in parallel, at least for reads?
By scarmig 3 hours ago
This immediately halves your throughput.
By Sesse__ 3 hours ago
Only in the worst case when the plans are equivalent: If one plan is significantly faster, then it'll finish first, and the loser can get canceled before it finishes.
By mattashii 3 hours ago
> 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.
By devsda 5 hours ago
On the flip side, its important to know just how much we would be sacrificing if big frontier gets their way in convincing the courts that distillation is a bad thing
By pyaamb 4 hours ago
Yeah don't steal my stolen stuff.
By globalnode 3 hours ago
Like this scene from Pirates of Silicon Valley between Bill Gates and Steve Jobs: https://www.youtube.com/watch?v=CBri-xgYvHQ Bill was saying that Steve was stealing from Xerox so it's okay if Bill stole from Xerox first.
By abirch an hour ago
I don't think anybody's denying that small models are doing distillation, the issue at hand is frontier lab vs frontier lab when it comes to big models.
By Onavo 5 hours ago
Frontier model trainers stole almost all the data they've trained on (the whole internet, all copyrighted).
It's very hard for them to claim the moral high ground here.
It's like stealing an apple from the British Colonial Empire.
By make3 4 hours ago
Having the moral high ground matters less than having a big warchest of money to spend on lawyers.
By p1necone 4 hours ago
Does it matter if they companies doing are not in the jurisdiction or even if they are, maybe the can't prove it?
By kelvinjps10 4 hours ago
They can't prove it but can force you to spend time and money to disprove it.
By Muromec 4 hours ago
The only thing they're going to try to do is ban Chinese models, and that's not going to fly outside the US, so Americans are going be the only losers.
By esafak 2 hours ago
You'd be shocked at some of the punishments handed out from various empires over history for offences as minor as stealing an apple. Whether they can claim the moral high ground is not question of ethics but a question of power.
By justonenote 4 hours ago
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?
By ashley95 37 minutes ago
> 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?
By Someone 5 hours ago
> ⇒ 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?
I think this would be likely comparable to a scheduled backup, so I think it would be an acceptable maintenance window. However, deterministic algorithms would likely beat re-training (or re-fine-tuning) the model. For example, one could analyze actual distributions or whatever (instead of assuming uniform), and then some plans would automatically be eliminated.
Imo a good thought experiment is to look at places that are hyper-optimized, like compilers. Would LLMs bring anything to the table (architecturally or performance-wise) to a piece of software that has been carefully crafted for decades? (Methinks no.)
By dvt 5 hours ago
The Postgres query planner has had to operate, for those same decades, in a much more realtime-sensitive and restricted environment than compilers. It can only draw its conclusions from summary statistics on tables in isolation, not on their relationships with each other (and even less so when filters are involved). For many cases this is fine! For many others it isn't.
By btown 4 hours ago
There's a good number of heuristic choices in compilation where, maybe, you could get more optimal outcomes with machine learning - but at the cost of compilation resources, both time and space, and possibly determinism too.
As an example, register allocation is graph colouring, and thus NP complete; a model for producing an allocation plan is learning heuristics that might look at more features in combination than the ones hand-crafted into the compiler. An LLM for the job might do better than a more focused model like a GNN, due to sheer size, the effectiveness of transformers, or magic. But it probably won't do an overall better job than the handcrafted heuristics, because those handcrafted heuristics also tend to compile very, very fast with a small memory footprint, and can be debugged (more) easily when they go wrong.
By codebje 2 hours ago
Could you stitch a specific query plan to your view?
‘create plan llm_optimized …’
‘create view foo (select x, y, z from table bar) with plan llm_optimized’
By bananaowl 2 hours ago
I think the idea would be making a frontier model that does this. One that is trained on multiple queries
By vatsachak 3 hours ago
the currency of the counts also, right.. actually recounting table contents is done from time to time
By mistrial9 2 hours ago
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?
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.
By BirdieNZ 2 hours ago
Mentioned elsewhere but classic ml seems the right tool for this problem
By darepublic 2 hours ago
> How hard can it be?
> As it turns out: enormously hard.
This exactly tracks me learning everything
By anitil 2 hours ago
Funny enough I was thinking about something very similar to this based on the Jev model posted yesterday.
By foota 4 hours ago
I’ve played with it already. I don’t think this is the use case. I think Jev’s use case is fast, cheap and somewhat easy classification. It’s not trainable in the way you would want here. Even though it’s fast it wont be faster than pgs query optimizer.
At least as I understand things.
How did you plan to use Jev for query optimization?
By jwpapi 4 hours ago
I am still struggling to understand a use-case for Jev. Isn't what was explained in this article a classification problem? I.e. find and aggregate data?
By orliesaurus 3 hours ago
The number of options has to be small and bounded. The query planning is more of a search/optimization problem than a classification problem since the number of options increases wildly based on query size.
By odo1242 3 hours ago
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.
By perrygeo an hour ago
such a (visually) beautiful blogpost.
By maxrumpf 4 hours ago
Aren't optimizations suppose to be deterministic?
By kingjimmy 5 hours ago
They are not. Choice among several query plans depends on various summary statistics about the data, which might not be the most recent.
By tintor 5 hours ago
Including the input parameters.
It's not unusual for us to end up with bad query plans because the shape of our data can vary pretty greatly. In many cases, a Foo has 1 Bar. But in some cases, a Foo has a million Bars. That can cause the query optimizer to treat lookups on the bar table as if there are few elements there (causing a scan instead of a seek).
For the general case, the optimizer gets it right. However, the fringe case is one that causes the entire system to crash. It's a bit akin to how an insertion sort can be faster than quick sort when n is small. The optimizer might make a bad assumption about the size of n which makes it pick an expensive n lookup when log(n) is available (but slower for small n).
By cogman10 5 hours ago
I'd like to contribute my amateur hour entry into this thread, although I did administer and develop mssql stuff for awhile.
sure optimizations based on stats, but the stats are the wildcard, in my experience query plans can change suddenly.
Queries are translated into plans according to statistics. However the transforms will be deterministic and should only change one valid plan to another. I could very easily see a neural network manipulate transforms the same way the current programming does, its just that the neural networks are by nature really nicely suitable because the "decisions" are based on training, and this training can be closed world type things like the ai assists that chess engines are now getting. Obviously ai still can't play chess but apparently its very good at ranking board positions just by developing that much statistical info because its training comes not from reading the web, but playing a gazzilian games against itself in a "closed" chess world of its own.
I'm thinking that the ai does "this legal transform of the query plan should be applied to this pattern of data (statistics, cardinality, etc)" simply because the ai encountered it in closed world training, much like the chess thing.
Just a theory tho feel free to correct!
By cowboylowrez 5 hours ago
> Obviously ai still can't play chess
I believe you are wrong on that. Do you mean large language models can’t play chess?
By krisoft 4 hours ago
I don't believe that a pure neural network can currently abide by the rules of chess, even with unreasonable amounts of training. Do you have a counter example? I never mind having beliefs challenged with facts lol
edit: I think you could provide an AI with a service or skill that asks "is this move legal" but given all the overhead for llms or whatever to call a "legal move" service external to its process, well then you aren't really searching the tree very efficiently lol.
However if you just let a neural network score boards and the neural network is in the same process well then I think thats the working solution for using neural networks in chess. The net does not need to score all boards either, simple value based heuristics can obviously provide a preliminary list of good boards (moves) at a certain depth or ply and then select the move that produces the board that the neural net scores highest. I kinda sorta think thats whats done today but as usual I could be full of it lol
By cowboylowrez 4 hours ago
There's quite a big difference between "AI" and "pure neural networks." No, frontier chess (Alpha zero etc) is not "pure," because there's Monte Carlo tree search and a hard-coded game rules implementation.
By cannonpalms 16 minutes ago
But how will you know that the query plan actually does what your query asked for?
By fsmv 6 hours ago
I would like to think that pg_hint_plan is designed in such a way that any hint it accepts must be a valid plan for the query. I’m quite confident that schemes with this property that can also express high quality plans are possible and not even excessively complicated.
This is not to say that it’s possible to genetically verify that a proposed algorithm does what you want it to — that would be undecidable or NP-hard or co-NP-hard depending on how you formulate the question.
By amluto 5 hours ago
I wouldn't be very excited about adding a 4B param model to my database deployment, but using this kind of approach while testing an app to identify query plans where Postgres is leaving performance on the table seems valuable without much risk.
By hedgehog 5 hours ago
If your statistics or workload change, this approach is useless. The hints are generated being generated ahead of time, taking 95hrs to do so.
By cannonpalms 14 minutes ago
Given the approach from the article, you can commit the hints to git and run tests for verification. The model would be used during coding.
By whazor 4 hours ago
`pg_hint_plan` has a debug log so you can verify Postgres actually used the hint or not! Used this during evaluations
By polyphilz 5 hours ago
You're misunderstanding the setup here. The LLM doesn't modify the query, just some details about how to choose between different ways to break the query into basic operations on the tables. The SQL doesn't change. It's still up to postgres to guarantee that the results match the query. If the proposed plan were nonsense that didn't amount to carrying out the query, postgres would ignore it.
By topaz0 2 hours ago
I imagine you can perform operations on query plans to transform them and determine equivalence?
By timcobb 6 hours ago
you'll have to prove equivalence through some Lean4 code perhaps? or some weird clause tree comparisons... good question indeed.
By larodi 4 hours ago
Because P!=NP (very probably IMHO) there's a huge class of problem for which LLMs are useful on the expensive and heuristic-y generation side because the verification is relatively inexpensive.
By quotemstr 4 hours ago
"... make no mistakes" :)
By KK7NIL 6 hours ago
TL:DR; for people. Index your data properly.
By trollied 3 hours ago
This is about getting the planner to more effectively use your 'proper indexes', concerned with the edge cases where it currently does not.
By stubish 39 minutes ago
why is this write-up so long?
Need 5 days just to go through it.
By coolThingsFirst 4 hours ago
They forgot to run a (same) model to optimize article for reading speed
By tandr 4 hours ago
Think of it like a paper. You wouldn't ask why a paper was so long.
Also you can now ask AI to summarise it for you and even probe with questions pertaining to your specific interests.
By fennecbutt 4 hours ago
Why wouldn't you ask that? That's exactly why papers have supplementary material
By gonzalohm 4 hours ago
> You wouldn't ask why a paper was so long.
I've asked myself that question a whole bunch of times
By refibrillator 4 hours ago
By dragontamer 3 hours ago
By soerxpso 3 hours ago
By setr an hour ago
By bt1a 2 hours ago
By dragontamer 31 minutes ago
By voganmother42 an hour ago
By tomnipotent an hour ago
By happyopossum 7 minutes ago
By sgarland an hour ago
By 2001zhaozhao 3 hours ago
By malisper 2 hours ago
By reval 44 minutes ago
By galkk 2 hours ago
By hamilyon2 4 hours ago
By topaz0 2 hours ago
By kccqzy 2 hours ago
By yipinwong 3 hours ago
By jaggederest 3 hours ago
By Sesse__ 3 hours ago
By haroldl 41 minutes ago
By HighlandSpring 3 hours ago
By scarmig 3 hours ago
By Sesse__ 3 hours ago
By mattashii 3 hours ago
By devsda 5 hours ago
By pyaamb 4 hours ago
By globalnode 3 hours ago
By abirch an hour ago
By Onavo 5 hours ago
By make3 4 hours ago
By p1necone 4 hours ago
By kelvinjps10 4 hours ago
By Muromec 4 hours ago
By esafak 2 hours ago
By justonenote 4 hours ago
By ashley95 37 minutes ago
By Someone 5 hours ago
By dvt 5 hours ago
By btown 4 hours ago
By codebje 2 hours ago
By bananaowl 2 hours ago
By vatsachak 3 hours ago
By mistrial9 2 hours ago
By huahaiy 2 hours ago
By BirdieNZ 2 hours ago
By darepublic 2 hours ago
By anitil 2 hours ago
By foota 4 hours ago
By jwpapi 4 hours ago
By orliesaurus 3 hours ago
By odo1242 3 hours ago
By perrygeo an hour ago
By maxrumpf 4 hours ago
By kingjimmy 5 hours ago
By tintor 5 hours ago
By cogman10 5 hours ago
By cowboylowrez 5 hours ago
By krisoft 4 hours ago
By cowboylowrez 4 hours ago
By cannonpalms 16 minutes ago
By fsmv 6 hours ago
By amluto 5 hours ago
By hedgehog 5 hours ago
By cannonpalms 14 minutes ago
By whazor 4 hours ago
By polyphilz 5 hours ago
By topaz0 2 hours ago
By timcobb 6 hours ago
By larodi 4 hours ago
By quotemstr 4 hours ago
By KK7NIL 6 hours ago
By trollied 3 hours ago
By stubish 39 minutes ago
By coolThingsFirst 4 hours ago
By tandr 4 hours ago
By fennecbutt 4 hours ago
By gonzalohm 4 hours ago
By meepmorp 31 minutes ago