4 ms·
Training a 4B model to produce 81% faster query plans than Postgres
- fsmv 18d agoBut how will you know that the query plan actually does what your query asked for?
- KK7NIL 18d ago"... make no mistakes" :)
- timcobb 18d agoI imagine you can perform operations on query plans to transform them and determine equivalence?
- polyphilz 18d ago`pg_hint_plan` has a debug log so you can verify Postgres actually used the hint or not! Used this during evaluations
- amluto 18d agoI 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.
- hedgehog 18d agoI 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.
- whazor 18d agoGiven the approach from the article, you can commit the hints to git and run tests for verification. The model would be used during coding.
- cannonpalms 17d agoIf your statistics or workload change, this approach is useless. The hints are generated being generated ahead of time, taking 95hrs to do so.
- quotemstr 18d agoBecause 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.
- larodi 18d agoyou'll have to prove equivalence through some Lean4 code perhaps? or some weird clause tree comparisons... good question indeed.
- topaz0 17d agoYou'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.
- poincareball 18d ago[dead]
- kingjimmy 18d agoAren't optimizations suppose to be deterministic?
- tintor 18d agoThey are not. Choice among several query plans depends on various summary statistics about the data, which might not be the most recent.
- cogman10 18d agoIncluding 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).
- cowboylowrez 18d agoI'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!
- Someone 18d 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?
- dvt 18d 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.)
- btown 18d agoThe 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.
- codebje 17d agoThere'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.
- devsda 18d 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.
- Onavo 18d agoI 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.
- make3 18d agoFrontier 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.
- p1necone 18d agoHaving the moral high ground matters less than having a big warchest of money to spend on lawyers.
- kelvinjps10 18d agoDoes it matter if they companies doing are not in the jurisdiction or even if they are, maybe the can't prove it?
- coolThingsFirst 18d agowhy is this write-up so long? Need 5 days just to go through it.
- tandr 18d agoThey forgot to run a (same) model to optimize article for reading speed
- fennecbutt 18d agoThink 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.
- vova_hn2 17d agoshould've made a TikTok video, instead of a write-up, amirite?
- foota 18d agoFunny enough I was thinking about something very similar to this based on the Jev model posted yesterday.
- jwpapi 18d agoI’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?
- orliesaurus 18d agoI 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?
- odo1242 18d agoThe 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.
- jwpapi 17d agoIt’s classifying faster and cheaper. A lot of immediate ideas are better solved by pre-classifying + embedding, but their doom example or the wikipedia runs are one where you can’t preclassify.
- refibrillator 18d ago“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.
- dragontamer 17d agoWith 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.
- soerxpso 17d agoI 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.
- setr 17d agoWith query hints finally being added it’d probably be doable as an extension
- williamdclt 17d agoPostgres takes the actual values into account when generating a query plan. The same query with different params can (and should) result in different query plans. It looks at statistics on the actual data stored.
- bt1a 17d agopardon but aren't disks usually the bottleneck? im all for CUDA acceleration and CUDA accelerating culture
- hamilyon2 18d agoOptimal 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.
- yipinwong 18d agoWhat 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.
- Sesse__ 18d agoThe immediate problem: How do you know which one is better without running them?
- scarmig 18d ago[flagged]
- Sesse__ 18d agoThis immediately halves your throughput.
- mattashii 18d agoOnly 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.
- 361994752 17d agoGood and bad plans can have orders of magnitude performance difference. The bad one can easily do enough damage cutting the performance in half before it is canceled.
- maxrumpf 18d agosuch a (visually) beautiful blogpost.
- deleted 18d ago[deleted]
- kevinbaiv 18d ago[flagged]
- 2001zhaozhao 18d agoEngineer: "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?"
- malisper 17d agoFunnily enough, you could replace "LLM query planner" with just "query planner" and this comment would still hold true
- deleted 17d ago[deleted]
- Tanjreeve 17d agoThat bug is fixable and verifiable. The LLM you cross your fingers till the next time the same thing happens.
- egeozcan 17d ago> fixable and verifiable By people with a specific skill set. LLMs generation can also be fixed and verified by people with a certain skill set, and non-deterministic computing doesn't automatically mean unpredictable. When people say that the LLMs are a black box, it means unpredictability in unknown situations. You do structured output, input validation, output validation, lower temperature, limit decisions, RL, etc. to increase predictability to near certainty. It's just statistics after all. Or you can as well generate the code to do the job. It's just that the required skill set is a different one to do those things, and unusual in the context of DB administration.
- Tanjreeve 17d agoOnly if someone is planning on running a pinned self hosted version of an LLM alongside the DB to fix the problem. The developer can change the binary easy enough and test it but the LLM approach just seems either theoretical or bending ourselves in knots to justify using an LLM.
- trollied 17d agoTL:DR; for people. Index your data properly.
- BirdieNZ 17d agoThis 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.
- huahaiy 17d ago81%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/benchmarks/JOB-bench https://github.com/datalevin/datalevin/tree/master/benchmark...
- tonetheman 17d ago[dead]
- anitil 17d ago> How hard can it be? > As it turns out: enormously hard. This exactly tracks me learning everything
- darepublic 17d agoMentioned elsewhere but classic ml seems the right tool for this problem
- perrygeo 17d agoNice 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.
- saiyamshah1496 17d ago[flagged]
- sgarland 17d agoUnless 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.
- deleted 17d ago[deleted]
- ashley95 17d agoSuppose 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?
- happyopossum 17d agoAn 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.
- basil_io 17d ago[dead]
- jerpint 17d agoI have a theory that soon enough every code library will ship a CLI and a very tiny finetune for that specific lib alongside it
- tobin1994 17d ago[dead]
- evaltoken 17d agoReally creative use of distillation here.
- aitoolcrux 17d ago[flagged]
- rixed 17d agoIt's certainly a good idea to train a neural network to find good query plans but... an LLM??
- HackerThemAll 17d agoTwo 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.
- karambahh 17d agoI'm also quite skeptical of performance/determinism over a real world load but if I'm not mistaken, a 10 000 qps with large shape of queries would not benefit much either from genetic algos, would it?
- HackerThemAll 16d agoI've seen this kind of performance coming out of PostgreSQL, so they do help somehow.
- zacmps 17d agoI 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.
- jneoioi 16d ago[dead]
- mohd_rafay 17d ago[flagged]
- rand_r 17d agoThe 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.
- itsmeduncan 17d ago[flagged]
- silverlinex 16d ago[dead]
- rtolkachev 16d ago[flagged]
- nuc1e0n 16d agoNeat. 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.
- tancop 16d ago> 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.