3 ms·
One of the biggest problems in database operations is that there are many potential query plans for a given query, but finding the optimal one can be difficult.
by InclinedPlane 15y ago
One of the biggest problems in database operations is that there are many potential query plans for a given query, but finding the optimal one can be difficult. More so, which query plan is optimal varies depending on table size.
If you let the DB pick its own query plan all the time you can end up with inconsistent performance, often by an order of magnitude or more. However, if you turn on query plan stability (every query after the first one will have the same plan) then you can end up with a state where the query plan doesn't change as the data change, which can lead to serious performance issues as a table's size grows (if your table starts out small, the DB may choose a full table scan, which is very fast, but will bog down extremely when the table grows to thousands or millions of row).
In short, performance is a hard problem, and if anything the tools for analyzing and optimizing performance for DB operations are well behind the state of the art for compiled code, for example.
- gaius 15y agoOracle is pretty smart about this, if a new plan is slower than the previous one, it seamlessly flips back.
- politician 15y agoJIT compilers have been doing hotspot recompilation for a while now. Is there a reason why databases haven't adopted the same techniques? Is there some barrier to caching multiple query plans optimized for different load scenarios?
- gaius 15y agoWhat do you mean by "load scenario"? The only thing that should affect the plan is the characteristics of the underlying data (e.g. do we expect to return so many rows that we should full-scan anyway, or can we use an index).