Training a 4B model to produce 81% faster query plans than Postgres
1–10 of 118 posts
Re: Training a 4B model to produce 81% faster query plans than Postgres
#2But how will you know that the query plan actually does what your query asked for?
Re: Training a 4B model to produce 81% faster query plans than Postgres
#3But how will you know that the query plan actually does what your query asked for?
"... make no mistakes" :)
Re: Training a 4B model to produce 81% faster query plans than Postgres
#4But how will you know that the query plan actually does what your query asked for?
I imagine you can perform operations on query plans to transform them and determine equivalence?
Re: Training a 4B model to produce 81% faster query plans than Postgres
#5But how will you know that the query plan actually does what your query asked for?
`pg_hint_plan` has a debug log so you can verify Postgres actually used the hint or not! Used this during evaluations
Re: Training a 4B model to produce 81% faster query plans than Postgres
#6But 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.
Re: Training a 4B model to produce 81% faster query plans than Postgres
#7[dead]
Re: Training a 4B model to produce 81% faster query plans than Postgres
#8Aren't optimizations suppose to be deterministic?
Re: Training a 4B model to produce 81% faster query plans than Postgres
#9But 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…
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.
Re: Training a 4B model to produce 81% faster query plans than Postgres
#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 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?