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

101–110 of 124 posts

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

#101

Post by FusionGaming »

dvt wrote: post #15
Someone wrote: post #10

> 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 mu…

> ⇒ 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…

I think there's a future in which LLMs are used for auto vectorization

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

#102

Post by dragontamer »

eloisius wrote: post #98
dragontamer wrote: post #56

Earlier quoted context omitted.

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.

What would you accelerate? Is there a lot of linear algebra you could throw cuda at in Postgres?

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 parallel sorting algorithm), its not even that difficult to implement anymore.

Naturally, this then leads to parallel Sort Merge Join, as well as parallel Hash-Join (two ways to implement left or right joins in a GPU that benefit from significant parallelism).

So yeah, Joins. https://www.kenchoi.dev/papers/gpu-joins.pdf (This paper also has a description of "Mergepath sort", a GPU parallel way of sorting)

---------

Even if GPUs weren't fundamentally faster at these kinds of operations... the RAM is simply 10x higher bandwidth and we all know its a RAM-constrained problem.

Your typical SQL query is going to need multiple joins, probably a sort and possibly some "group" operations. As long as you have more than 10,000 elements or so (IE: can saturate all 10,000+ SIMD-units of a GPU), you'll be able to at least benefit from the faster RAM.

If you have a LOT of joins (a recursive join or some other kind of deeply nested computationally complex query), you probably benefit even more from the greater compute-power offered by GPUs. These operations (joins really) are nominally over the entire set of data, and cleanly break down into obvious parallelism.

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

#103

Post by dnautics »

refibrillator wrote: post #29

“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 pr…

I think in principle you could clone your database in prod and at least test to see if your most difficult + common queries are indeed faster after running through the LLM optimizer?

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

#104

Post by dnautics »

kccqzy wrote: post #65
hamilyon2 wrote: post #32

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.

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.

I think your CPU might even have a small neural net in the branch predictor

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

#105

Post by [deleted] »

2001zhaozhao wrote: post #52

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

[deleted]

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

#107

Post by saghm »

dragontamer wrote: post #88
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

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.

Yeah, I have a GPU from almost six years ago in my desktop that has twice that much VRAM. Less than a year ago my wife got a 5070 Ti with the same for around $750 without needing to wait for it to be in stock or anything. I'm inclined to think that for a server that needs a GPU, even 32 GB would probably be considered small.

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

#108

Post by Tanjreeve »

malisper wrote: post #63
2001zhaozhao wrote: post #52

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

Funnily enough, you could replace "LLM query planner" with just "query planner" and this comment would still hold true

That bug is fixable and verifiable. The LLM you cross your fingers till the next time the same thing happens.

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

#109

Post by egeozcan »

Tanjreeve wrote: post #108
malisper wrote: post #63

Earlier quoted context omitted.

Funnily enough, you could replace "LLM query planner" with just "query planner" and this comment would still hold true

That bug is fixable and verifiable. The LLM you cross your fingers till the next time the same thing happens.

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

Post reply on HN