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

141–150 of 150 posts

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

#141

Post by malisper »

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.

> That bug is fixable and verifiable

A bad query plan is not your typical kind of bug. I would definitely not call it fixable. Query planners are inherently dealing with estimations and approximations. If the query planners estimation is off, you're screwed.

Unless you come up with a way to cheaply determine exactly how many rows a query will return, bad query plans will still exist.

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

#142

Post by danielbarla »

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

Indeed. I'm honestly shocked that we're still having to evict bad query plans in 2026. And "you changed a variable", ha, that sounds like an actual reason. How about "data statistics were automatically refreshed and you hit some magical undocumented heuristic threshold, an the query that ran in 35ms yesterday now takes 45 minutes. And we can actually tell you this because we have the data, but decided to let you find out manually, instead."

I've literally been saying "I can't believe the date is X and we still have to put up with this" for around 25 years now.

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

#143

Post by wodenokoto »

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

I’m more worried that the query planner requires more compute than the query.

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

#145

Post by swiftcoder »

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.

> The GPU isn’t connected to the disk though. Usually.

It can be. That was the big new innovation in video game load times

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

#146

Post by yipinwong »

Sesse__ wrote: post #46
yipinwong wrote: post #45

Earlier quoted context omitted.

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?

That's why I am a newbie for DB. I do not know how QO does that in the first place...

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

#147

Post by malisper »

tempfile wrote: post #136
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…

None of the things you mention are guaranteed to increase the probability of correctness. You can run the LLM output through as many deterministic programs as you like, but "the query plan runs in acceptable time" is not something you can verify with such a tool. Nobody knows how the LLM does it, so they cannot know how to make the LLM do it better .

Even if the query plan was not generated by an llm, you can't verify it will run in an acceptable time. This is one of the biggest unsolved problems in databases

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

#148

Post by mr_toad »

simondotau wrote: post #100
reval wrote: post #83

Earlier quoted context omitted.

I’ve seen this happen to SQL Server many times. Every time the solution is a stored proc with the recompile option enabled.

The answer to every problem is to rebuild statistics. (And never use stored procedures. It's just a shitty API layer in the worst language imaginable, sitting outside of source control. If you need an API layer, write it in a real language, ideally the one you're already using.)

In my experience with databases the answer is never never, and never always, and it’s almost always sometimes and maybe.

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

#149

Post by egeozcan »

tempfile wrote: post #136
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…

None of the things you mention are guaranteed to increase the probability of correctness. You can run the LLM output through as many deterministic programs as you like, but "the query plan runs in acceptable time" is not something you can verify with such a tool. Nobody knows how the LLM does it, so they cannot know how to make the LLM do it better .

> Nobody knows how the LLM does it

From a completely technical perspective, we have a rough idea how the LLMs work, and improving a system requires measuring outcomes and you don't necessarily need to understand the mechanism.

EXPLAIN ANALYZE against data that's similar in size to prod checks a query written by an LLM as good as anything we can write... but, yes, you're right, we still didn't solve the halting problem - neither the LLMs.

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

#150

Post by sandeepkd »

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 had the similar feelings, the setup is biased for certain outcomes it feels. At times I feel like that I am in an eternal questioning mode but then again I find it to be a better choice to be critical and skeptical for technology related things.

Couple things that I found interesting

1. Inefficiencies/limitations of the query planner in certain cases are known for a long time, its a trade off. This is the reason why hints exists and one can provide their own plan too. DBAs have been doing that for a while now.

2. A SQL database by design is a resilient unit in itself just dependent on CPU and memory/disk. The availability for the DB is heavily dependent on this factor. Everything built on the top derives their availability and reliability from this. Adding an LLM in between is more cost for sure, question is if its really brining the benefits which are worth the trade off

Post reply on HN