Comment by yipinwong
13 hours ago
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.
13 hours ago
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.
Yes, but it's exactly the kind of hybrid between a regular planner and something generative (writ broadly) that they were asking about. Practically speaking if you're hitting the GEQO you've already failed as a query writer unless it's a purely OLAP on a dedicated beefy machine.
1 reply →
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.
2 replies →
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.)
You can and some databases can do this (e.g. Oracle).
Customers love it when their queries sometimes run a lot longer.
[flagged]
This immediately halves your throughput.
2 replies →