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

81–90 of 124 posts

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

#81

Post by tomnipotent »

bt1a wrote: post #72
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.

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

Which is why a good query plan is so important, so that as much disk I/O can be avoided as possible (predicate push down, index elimination, join ordering, partition/scan pruning). Like the old CTE optimization fence problem.

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

#82

Post by sgarland »

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

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

#83

Post by reval »

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’ve seen this happen to SQL Server many times. Every time the solution is a stored proc with the recompile option enabled.

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

#84

Post by haroldl »

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?

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

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

#86

Post by ashley95 »

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

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

#87

Post by meepmorp »

fennecbutt wrote: post #27
coolThingsFirst wrote: post #18

why is this write-up so long? Need 5 days just to go through it.

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

> You wouldn't ask why a paper was so long.

I've asked myself that question a whole bunch of times

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

#88

Post by dragontamer »

bt1a wrote: post #72
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.

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.

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

#89

Post by cannonpalms »

cowboylowrez wrote: post #39
krisoft wrote: post #35

Earlier quoted context omitted.

> Obviously ai still can't play chess I believe you are wrong on that. Do you mean large language models can’t play chess?

I don't believe that a pure neural network can currently abide by the rules of chess, even with unreasonable amounts of training. Do you have a counter example? I never mind having beliefs challenged with facts lol edit: I think you could provide an AI with a service or skill that asks "is this move legal" but given all the overhead for llms or whatever to call a "legal move" service external to its process, well the…

There's quite a big difference between "AI" and "pure neural networks." No, frontier chess (Alpha zero etc) is not "pure," because there's Monte Carlo tree search and a hard-coded game rules implementation.

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

#90

Post by cannonpalms »

hedgehog wrote: post #9
amluto wrote: post #6

Earlier quoted context omitted.

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

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

If your statistics or workload change, this approach is useless. The hints are generated being generated ahead of time, taking 95hrs to do so.
Post reply on HN