Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

rohanbansal.com

Post reply on HN
Print view View original

121–130 of 132 posts

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

#121

Post by jaggederest »

Sesse__ wrote: post #118
jaggederest wrote: post #57

Earlier quoted context omitted.

This is about to bake your noodle: https://www.postgresql.org/docs/current/geqo-pg-intro.html

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

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

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

#122

Post by mike_hearn »

HighlandSpring wrote: post #54
Sesse__ wrote: post #46

Earlier quoted context omitted.

The immediate problem: How do you know which one is better without running them?

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?

You can and some databases can do this (e.g. Oracle).

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

#123

Post by egeozcan »

Tanjreeve wrote: post #120
egeozcan wrote: post #109

Earlier quoted context omitted.

> 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, et…

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

I didn't argue that it'd make sense, I just said that it could be reasonably fixable when problems occur and verifyable that the fix works. Even if shipping and RLing an LLM were easy tasks in terms of software distribution (they are not) we'd still hit the skill mismatch, as I said in my previous comment.

I just find the "all llms are non dererministic and therefore unreliable" narrative a bit backwards. All software that has more than 0 users needs to deal with non-determinism anyway :)

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

#124

Post by jwpapi »

orliesaurus wrote: post #42
jwpapi wrote: post #37

Earlier quoted context omitted.

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?

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?

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

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

#125

Post by Sesse__ »

jaggederest wrote: post #121
Sesse__ wrote: post #118

Earlier quoted context omitted.

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

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

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

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

#126

Post by CodesInChaos »

fuy wrote: post #113
bt1a wrote: post #72

Earlier quoted context omitted.

pardon but aren't disks usually the bottleneck? im all for CUDA acceleration and CUDA accelerating culture

not necessarily, no. With SSDs you get much better IOPS for cold data, and many datasets fit in RAM. So a lot of (OLTP/HTAP) workloads can become CPU-bound due to sorting/hashing - bread and butter of joins.

Though many use network block storage (e.g. EBS), which is significantly slower than an SSD.

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

#127

Post by yfontana »

withinboredom wrote: post #114
dragontamer wrote: post #102

Earlier quoted context omitted.

You know that GPUs are more flexible than just linear algebra, right? GPUs are simply faster at fundamental algorithms like sorting (which has huge parallelism), and hashing. This is because both sorting and hashing benefit from endless growth of parallelism, offering enough "work" for these 10,000 SIMD-core systems to crunch work upon. And because of modern algorithms/libraries with 'Mergepath sort' (a GPU-SIMD para…

The GPU isn’t connected to the disk though. Usually. So you’d still have to load from disk, to ram, then from ram to the GPU.

[dead]

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

#128

Post by locknitpicker »

Sesse__ wrote: post #112
haroldl wrote: post #84

Earlier quoted context omitted.

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

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

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

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

#129

Post by pbalau »

Sesse__ wrote: post #112
haroldl wrote: post #84

Earlier quoted context omitted.

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

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

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

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

#130

Post by HackerThemAll »

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.

Post reply on HN