5 ms·
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 grow
by hamilyon2 12d 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.
- yipinwong 12d 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__ 12d agoThe immediate problem: How do you know which one is better without running them?
- scarmig 12d ago[flagged]
- Sesse__ 12d agoThis immediately halves your throughput.
- mattashii 12d 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 12d 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.
- HighlandSpring 12d agoCould 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?
- adrianN 11d agoCustomers love it when their queries sometimes run a lot longer.
- Sesse__ 11d agoThere are papers and Postgres projects that attempt this kind of learning-based optimization, with some success. None are in widespread use. (One part, but certainly not the entirety, of the problem is that it's not just A/B, it's an exponential number of options that all could seem close to each other.)
- mike_hearn 11d agoYou can and some databases can do this (e.g. Oracle).
- haroldl 12d agoYou 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".
- Sesse__ 11d agoIf you have formulas that actually match reality, what do you need the LLM for? An optimizer is perfectly capable of finding the optimal plan if it has a perfect estimator. In fact, if you could only estimate the number of rows in each subplan perfectly, you have as good as solved the problem already.
- locknitpicker 11d ago> If you have formulas that actually match reality, what do you need the LLM for? That's the key question. I think LLMs allow people with no context or background or know-how to dive into projects and see some results being presented to them, but they don't have the context or skillset to tell what they see before them. This paves the way to people laying grand claims about achievements because of LLMs. Their claim is that LLMs know best primarily because LLMs knew more than them, not that the output is good or desirable.
- pbalau 11d ago> If you have formulas that actually match reality, what do you need the LLM for? Because one could be in that state where they are trying to use a tech they know preciously little about to solve a problem they know nothing about. This reminds me of a request we got from our "AI Department": if you build us a proper shares market simulator, we will build you an awesome agent that can trade shares. They seemed quite confused when I pointed out that if we could build such a simulator, we wouldn't need them anymore.
- ants_a 10d agoThis is how the built in planner works already. It generates all possible plans and picks the one with the lowest cost. But calculating the cost is based on statistics and models, and these are wrong. Usually useful, but always wrong.
- yipinwong 11d agoThat's why I am a newbie for DB. I do not know how QO does that in the first place...
- jaggederest 12d agoThis is about to bake your noodle: https://www.postgresql.org/docs/current/geqo-pg-intro.html https://www.postgresql.org/docs/current/geqo-pg-intro.html
- Sesse__ 11d agoGEQO is not to get a better plan than the traditional optimizer, it is to be able to get a plan at all when the query is large. And it's widely known for creating poor plans.
- jaggederest 11d agoYes, but it's exactly the kind of hybrid between a regular planner and something generative (writ broadly) that they were asking about. Practically speaking if you're hitting the GEQO you've already failed as a query writer unless it's a purely OLAP on a dedicated beefy machine.
- Sesse__ 11d agoI think calling GEQO generative is a bit of a stretch; it's just a different way of searching through the same space with the same cost model. More or less devolving to “let's take a bunch of randomized join orders and see which one is best” :-) And yes, large joins is definitely for OLAP use. If you have 20-way joins for OLTP, you're either crazy or you're using an ORM.
- deleted 12d ago[deleted]
- kccqzy 12d agoIt’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.
- dnautics 11d agoI think your CPU might even have a small neural net in the branch predictor
- topaz0 12d agoI 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.
- caycep 11d agoOT but when is LLM not a blunt weapon?