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.

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

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

Our database 12.34 was working great, but 12.35 deployment had some optimizer changes that had regression on exact scenario that you have in your statistics. Shit happens, sorry. Use this hint.

I also wondered why an LLM would be the right starting point. Why would Balzac or billions of lines of rwir code or reddit be relevant to mapping this smallish, well-defined language (SQL) to this other tiny constrained specification language (the query plan suggestions)? You could make a (relatively) tiny network and then actually pass it some relevant features of the actual data, like as numbers, not just as text returned from a tool call.

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.

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.

On the flip side, its important to know just how much we would be sacrificing if big frontier gets their way in convincing the courts that distillation is a bad thing

Yeah don't steal my stolen stuff.

I don't think anybody's denying that small models are doing distillation, the issue at hand is frontier lab vs frontier lab when it comes to big models.

Frontier model trainers stole almost all the data they've trained on (the whole internet, all copyrighted).

It's very hard for them to claim the moral high ground here.

It's like stealing an apple from the British Colonial Empire.

> ⇒ 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 actual distributions or whatever (instead of assuming uniform), and then some plans would automatically be eliminated.

Imo a good thought experiment is to look at places that are hyper-optimized, like compilers. Would LLMs bring anything to the table (architecturally or performance-wise) to a piece of software that has been carefully crafted for decades? (Methinks no.)

Could you stitch a specific query plan to your view?

‘create plan llm_optimized …’

‘create view foo (select x, y, z from table bar) with plan llm_optimized’

I think the idea would be making a frontier model that does this. One that is trained on multiple queries

the currency of the counts also, right.. actually recounting table contents is done from time to time

I’ve played with it already. I don’t think this is the use case. I think Jev’s use case is fast, cheap and somewhat easy classification. It’s not trainable in the way you would want here. Even though it’s fast it wont be faster than pgs query optimizer.

At least as I understand things.

How did you plan to use Jev for query optimization?

They are not. Choice among several query plans depends on various summary statistics about the data, which might not be the most recent.

I'd like to contribute my amateur hour entry into this thread, although I did administer and develop mssql stuff for awhile.

sure optimizations based on stats, but the stats are the wildcard, in my experience query plans can change suddenly.

Queries are translated into plans according to statistics. However the transforms will be deterministic and should only change one valid plan to another. I could very easily see a neural network manipulate transforms the same way the current programming does, its just that the neural networks are by nature really nicely suitable because the "decisions" are based on training, and this training can be closed world type things like the ai assists that chess engines are now getting. Obviously ai still can't play chess but apparently its very good at ranking board positions just by developing that much statistical info because its training comes not from reading the web, but playing a gazzilian games against itself in a "closed" chess world of its own.

I'm thinking that the ai does "this legal transform of the query plan should be applied to this pattern of data (statistics, cardinality, etc)" simply because the ai encountered it in closed world training, much like the chess thing.

Just a theory tho feel free to correct!

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 or co-NP-hard depending on how you formulate the question.

`pg_hint_plan` has a debug log so you can verify Postgres actually used the hint or not! Used this during evaluations

You're misunderstanding the setup here. The LLM doesn't modify the query, just some details about how to choose between different ways to break the query into basic operations on the tables. The SQL doesn't change. It's still up to postgres to guarantee that the results match the query. If the proposed plan were nonsense that didn't amount to carrying out the query, postgres would ignore it.

I imagine you can perform operations on query plans to transform them and determine equivalence?

you'll have to prove equivalence through some Lean4 code perhaps? or some weird clause tree comparisons... good question indeed.

Because P!=NP (very probably IMHO) there's a huge class of problem for which LLMs are useful on the expensive and heuristic-y generation side because the verification is relatively inexpensive.

"... make no mistakes" :)

It’s 8G of data in memory. You can raw dog that no problem.

(Indexes would be nice though)

This is about getting the planner to more effectively use your 'proper indexes', concerned with the edge cases where it currently does not.

They forgot to run a (same) model to optimize article for reading speed

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.