“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 profile guided optimization. Many of us appreciate how database workloads can drift over time and with scale.
Kudos to the author for getting their hands dirty and writing up their experiments.
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.
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.
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.
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?"
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.
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.
Optimal plan construction is math-heavy, algorithm-heavy and vary even by workload. There are options like creating just-in-time indexes, so solution space grows even faster than article presents. Sometimes it is the query planner which is the slow part of total execution time.
LLM is kind of blunt weapon to use here. I am waiting rather for alphago style neural net heuristic.
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.
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".
Only in the worst case when the plans are equivalent: If one plan is significantly faster, then it'll finish first, and the loser can get canceled before it finishes.
Good and bad plans can have orders of magnitude performance difference. The bad one can easily do enough damage cutting the performance in half before it is canceled.
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
Like this scene from Pirates of Silicon Valley between Bill Gates and Steve Jobs: https://www.youtube.com/watch?v=CBri-xgYvHQ Bill was saying that Steve was stealing from Xerox so it's okay if Bill stole from Xerox first.
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.
The only thing they're going to try to do is ban Chinese models, and that's not going to fly outside the US, so Americans are going be the only losers.
You'd be shocked at some of the punishments handed out from various empires over history for offences as minor as stealing an apple. Whether they can claim the moral high ground is not question of ethics but a question of power.
> 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 much), but I suspect they didn’t include those ~95 hours in the benchmark numbers.
I think all database vendors know their query optimizers could do much better if they could afford to spend lots of time to derive query plans.
⇒ 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?
> ⇒ 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.)
The Postgres query planner has had to operate, for those same decades, in a much more realtime-sensitive and restricted environment than compilers. It can only draw its conclusions from summary statistics on tables in isolation, not on their relationships with each other (and even less so when filters are involved). For many cases this is fine! For many others it isn't.
There's a good number of heuristic choices in compilation where, maybe, you could get more optimal outcomes with machine learning - but at the cost of compilation resources, both time and space, and possibly determinism too.
As an example, register allocation is graph colouring, and thus NP complete; a model for producing an allocation plan is learning heuristics that might look at more features in combination than the ones hand-crafted into the compiler. An LLM for the job might do better than a more focused model like a GNN, due to sheer size, the effectiveness of transformers, or magic. But it probably won't do an overall better job than the handcrafted heuristics, because those handcrafted heuristics also tend to compile very, very fast with a small memory footprint, and can be debugged (more) easily when they go wrong.
This was a thoroughly enjoyable read, both the writing and presentation. I really liked the level of writing as it's basically introducing a whole lot of advanced topics but at just the right level for a non-AI researcher type of engineer like myself to be able to understand what's going on, and I felt it made some elements of LLMs actually something I could understand rather than wizardry done by maths PhDs. Probably because it's more like applied engineering rather than hard mathematics here. Thank you for a delightful post.
81%is nothing. It is not hard to be more than 3x better than Postgres [1]. And you don’t need a model to do that, let alone a 4B model. How much additional compute is needed to just run that model?
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?
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?
I am still struggling to understand a use-case for Jev. Isn't what was explained in this article a classification problem? I.e. find and aggregate data?
The number of options has to be small and bounded. The query planning is more of a search/optimization problem than a classification problem since the number of options increases wildly based on query size.
Nice article about how to train/fine-tune a language model.
However, it misses the whole point of database query planning. You can't just ignore the planning time itself, as if the database query were a static entity to be optimized once at a leisurely pace.
The real constraint on live query planners is quite different: they must improve the combined time - planning + query - based on live database statistics. You can amortize the planning with prepared statements, but that too is fraught since optimal plans can change quite frequently and based on input parameters. "Live" and "faster than the queries themselves" are the hard requirements to be considered a viable database query planner. This project does neither.
It's not unusual for us to end up with bad query plans because the shape of our data can vary pretty greatly. In many cases, a Foo has 1 Bar. But in some cases, a Foo has a million Bars. That can cause the query optimizer to treat lookups on the bar table as if there are few elements there (causing a scan instead of a seek).
For the general case, the optimizer gets it right. However, the fringe case is one that causes the entire system to crash. It's a bit akin to how an insertion sort can be faster than quick sort when n is small. The optimizer might make a bad assumption about the size of n which makes it pick an expensive n lookup when log(n) is available (but slower for small n).
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.
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 then you aren't really searching the tree very efficiently lol.
However if you just let a neural network score boards and the neural network is in the same process well then I think thats the working solution for using neural networks in chess. The net does not need to score all boards either, simple value based heuristics can obviously provide a preliminary list of good boards (moves) at a certain depth or ply and then select the move that produces the board that the neural net scores highest. I kinda sorta think thats whats done today but as usual I could be full of it lol
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.
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.
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.
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.
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.
“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 profile guided optimization. Many of us appreciate how database workloads can drift over time and with scale.
Kudos to the author for getting their hands dirty and writing up their experiments.
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.
With query hints finally being added it’d probably be doable as an extension
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.
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.
I remember projects like PG-Strom back in the day, very cool stuff
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
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.
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.
Optimal plan construction is math-heavy, algorithm-heavy and vary even by workload. There are options like creating just-in-time indexes, so solution space grows even faster than article presents. Sometimes it is the query planner which is the slow part of total execution time.
LLM is kind of blunt weapon to use here. I am waiting rather for alphago style neural net heuristic.
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.
This is about to bake your noodle:
https://www.postgresql.org/docs/current/geqo-pg-intro.html
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".
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?
Not someone familiar with DB internals, but could you run in parallel, at least for reads?
This immediately halves your throughput.
Only in the worst case when the plans are equivalent: If one plan is significantly faster, then it'll finish first, and the loser can get canceled before it finishes.
Good and bad plans can have orders of magnitude performance difference. The bad one can easily do enough damage cutting the performance in half before it is canceled.
> Frontier intelligence is extremely powerful; the distillation I did off Astra trajectories is proof enough that large models are not going anywhere
Wouldn't admitting this invite trouble due to accusations of distillation flying around between closed and open models.
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.
Like this scene from Pirates of Silicon Valley between Bill Gates and Steve Jobs: https://www.youtube.com/watch?v=CBri-xgYvHQ Bill was saying that Steve was stealing from Xerox so it's okay if Bill stole from Xerox first.
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.
Having the moral high ground matters less than having a big warchest of money to spend on lawyers.
Does it matter if they companies doing are not in the jurisdiction or even if they are, maybe the can't prove it?
They can't prove it but can force you to spend time and money to disprove it.
The only thing they're going to try to do is ban Chinese models, and that's not going to fly outside the US, so Americans are going be the only losers.
Don't see the problem here actually.
You'd be shocked at some of the punishments handed out from various empires over history for offences as minor as stealing an apple. Whether they can claim the moral high ground is not question of ethics but a question of power.
> 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 much), but I suspect they didn’t include those ~95 hours in the benchmark numbers.
I think all database vendors know their query optimizers could do much better if they could afford to spend lots of time to derive query plans.
⇒ 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?
> ⇒ 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.)
The Postgres query planner has had to operate, for those same decades, in a much more realtime-sensitive and restricted environment than compilers. It can only draw its conclusions from summary statistics on tables in isolation, not on their relationships with each other (and even less so when filters are involved). For many cases this is fine! For many others it isn't.
There's a good number of heuristic choices in compilation where, maybe, you could get more optimal outcomes with machine learning - but at the cost of compilation resources, both time and space, and possibly determinism too.
As an example, register allocation is graph colouring, and thus NP complete; a model for producing an allocation plan is learning heuristics that might look at more features in combination than the ones hand-crafted into the compiler. An LLM for the job might do better than a more focused model like a GNN, due to sheer size, the effectiveness of transformers, or magic. But it probably won't do an overall better job than the handcrafted heuristics, because those handcrafted heuristics also tend to compile very, very fast with a small memory footprint, and can be debugged (more) easily when they go wrong.
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 have a theory that soon enough every code library will ship a CLI and a very tiny finetune for that specific lib alongside it
An 8GB dataset? That’s literally a few seconds worth of records generated in my world, and any speedup at that scale is completely meaningless.
Let’s talk when you are looking at double digit TB at a minimum.
This was a thoroughly enjoyable read, both the writing and presentation. I really liked the level of writing as it's basically introducing a whole lot of advanced topics but at just the right level for a non-AI researcher type of engineer like myself to be able to understand what's going on, and I felt it made some elements of LLMs actually something I could understand rather than wizardry done by maths PhDs. Probably because it's more like applied engineering rather than hard mathematics here. Thank you for a delightful post.
81%is nothing. It is not hard to be more than 3x better than Postgres [1]. And you don’t need a model to do that, let alone a 4B model. How much additional compute is needed to just run that model?
[1] https://github.com/datalevin/datalevin/tree/master/benchmark...
Really creative use of distillation here.
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?
Funny enough I was thinking about something very similar to this based on the Jev model posted yesterday.
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?
I am still struggling to understand a use-case for Jev. Isn't what was explained in this article a classification problem? I.e. find and aggregate data?
The number of options has to be small and bounded. The query planning is more of a search/optimization problem than a classification problem since the number of options increases wildly based on query size.
Mentioned elsewhere but classic ml seems the right tool for this problem
> How hard can it be?
> As it turns out: enormously hard.
This exactly tracks me learning everything
Nice article about how to train/fine-tune a language model.
However, it misses the whole point of database query planning. You can't just ignore the planning time itself, as if the database query were a static entity to be optimized once at a leisurely pace.
The real constraint on live query planners is quite different: they must improve the combined time - planning + query - based on live database statistics. You can amortize the planning with prepared statements, but that too is fraught since optimal plans can change quite frequently and based on input parameters. "Live" and "faster than the queries themselves" are the hard requirements to be considered a viable database query planner. This project does neither.
Aren't optimizations suppose to be deterministic?
They are not. Choice among several query plans depends on various summary statistics about the data, which might not be the most recent.
Including the input parameters.
It's not unusual for us to end up with bad query plans because the shape of our data can vary pretty greatly. In many cases, a Foo has 1 Bar. But in some cases, a Foo has a million Bars. That can cause the query optimizer to treat lookups on the bar table as if there are few elements there (causing a scan instead of a seek).
For the general case, the optimizer gets it right. However, the fringe case is one that causes the entire system to crash. It's a bit akin to how an insertion sort can be faster than quick sort when n is small. The optimizer might make a bad assumption about the size of n which makes it pick an expensive n lookup when log(n) is available (but slower for small n).
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!
> 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 then you aren't really searching the tree very efficiently lol.
However if you just let a neural network score boards and the neural network is in the same process well then I think thats the working solution for using neural networks in chess. The net does not need to score all boards either, simple value based heuristics can obviously provide a preliminary list of good boards (moves) at a certain depth or ply and then select the move that produces the board that the neural net scores highest. I kinda sorta think thats whats done today but as usual I could be full of it lol
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.
such a (visually) beautiful blogpost.
But how will you know that the query plan actually does what your query asked for?
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.
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.
Given the approach from the article, you can commit the hints to git and run tests for verification. The model would be used during coding.
If your statistics or workload change, this approach is useless. The hints are generated being generated ahead of time, taking 95hrs to do so.
`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" :)
TL:DR; for people. Index your data properly.
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.
why is this write-up so long?
Need 5 days just to go through it.
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.
Why wouldn't you ask that? That's exactly why papers have supplementary material
> You wouldn't ask why a paper was so long.
I've asked myself that question a whole bunch of times