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

131–140 of 140 posts

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

#131

Post by __alexs »

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.

PCI-E is really very flexible https://developer.nvidia.com/gpudirect

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

#132

Post by zacmps »

I am skeptical of these results given that the end has:

> Favorite settings The model regularly used enable_sort=off and random_page_cost=1.1

If random_page_cost wasn't set correctly for the default cases postgres's query planner can generate terrible plans (unless you're running on a spinning disk).

That could easily explain the difference by itself.

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

#133

Post by williamdclt »

soerxpso wrote: post #58
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.

I would think it's possible to make it so that the 4B model only needs to be called during an initial phase, and then the same queries it constructed can just be re-used with values replaced, unless you're generating a lot of unique on-the-fly query shapes.

Postgres takes the actual values into account when generating a query plan. The same query with different params can (and should) result in different query plans. It looks at statistics on the actual data stored.

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

#134

Post by karambahh »

HackerThemAll wrote: post #130

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.

I'm also quite skeptical of performance/determinism over a real world load but if I'm not mistaken, a 10 000 qps with large shape of queries would not benefit much either from genetic algos, would it?

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

#136

Post by tempfile »

egeozcan wrote: post #109
Tanjreeve wrote: post #108

Earlier quoted context omitted.

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

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

#137

Post by rand_r »

The end game is adaptive query plans.

A big reason the initial plan isn't guaranteed to be optimal, even with all the right indexes, is that table statistics aren't perfect. For example, you might track a column's correlation (how closely the column's logical ordering matches its physical ordering in the heap), but that won't be broken down at a per value level. Postal code X might be very correlated, while postal code Y that is used in your query is completely uncorrelated.

The ideal solution is to pick one plan initially, and then update a temporary query-specific statistic model based on the data you actually read while executing the query. Then periodically re-evaluate if an alternative plan would be faster, switching to it in a way that doesn't throw away the current partial result.

Of course switching plans mid flight is very complicated, but Oracle and SQL server both support this feature, so hopefully it lands in Postgres at some point.

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

#138

Post by Sesse__ »

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…

> For example, one could analyze _actual_ distributions or whatever (instead of assuming uniform)

Postgres keeps histograms (including N most common values) for all columns; it does not blindly assume uniform distributions. (Presumably an LLM would have access to the same histograms.)

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

#139

Post by websap »

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

Sounds like a skill issue if your infra can't validate these changes.

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

#140

Post by d0100 »

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.

I've reduced queries from minutes long to 2s in Postgres by duplicating a CTE and keeping it unused

Query planner feels pretty LLM-esque already

Post reply on HN