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

111–120 of 124 posts

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

#111

Post by vidarh »

dnautics wrote: post #103
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?

Frankly, just a general extension to feed a query log to a batch job to do offline optimisation of common actual reoccurring query shapes based on a query log might well be worth it.

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

#112

Post by Sesse__ »

haroldl wrote: post #84
Sesse__ wrote: post #46

Earlier quoted context omitted.

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

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.

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

#113

Post by fuy »

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

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.

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

#114

Post by withinboredom »

dragontamer wrote: post #102
eloisius wrote: post #98

Earlier quoted context omitted.

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

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

#115

Post by [deleted] »

sgarland wrote: post #82

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

[deleted]

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

#116

Post by Sesse__ »

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?

There are papers and Postgres projects that attempt this kind of learning-based optimization, with some success. None are in widespread use. (One part, but certainly not the entirety, of the problem is that it's not just A/B, it's an exponential number of options that all could seem close to each other.)

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

#117

Post by ehe78qhe »

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

This exact situation has happened to me with Postgres' stock query planner when an automatic analyze got a bad sample of a large table.

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

#118

Post by Sesse__ »

jaggederest wrote: post #57
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.

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.

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

#119

Post by locknitpicker »

dnautics wrote: post #103
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?

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

That is the responsibility of whoever thought it would be a good idea to write this article. It's their responsibility to show that their idea has merit, and that their results are significant. I mean, don't they have a vested interest in manipulating and cherry-picking their results to inflate their relevance?

This is why academic papers are peer reviewed.

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

#120

Post by Tanjreeve »

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…

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.
Post reply on HN