3 ms·
Whether that is a good idea or not depends on a lot of factors. Having a cost based query planner is a huge advantage most of the time. As you say data and sta
by funcDropShadow 5y ago
Whether that is a good idea or not depends on a lot of factors. Having a cost based query planner is a huge advantage most of the time. As you say data and statistics change over time. But that also means, that the optimal query plan might change over time.
Even if your extensions sets the query plan in stone, you might have severe degradation of query performance over time, because now a different plan might be optimal.
Additionally, the optimal query plan can depend on the parameter values of a query.
- gurjeet 5y agoJust thought of this idea, let me know what you/others think. How about introducing a parameter that controls what percentage of time the frozen-plan will be used, and the rest of the time the regular optimizer will be allowed to create a new plan based on current data and statistics. This will allow the users to choose the mix. So if I were to use this feature, I'd set it to use frozen-pan 90% of the time, and 10% of the time the most-current plan. This way, irrespective of whether the most current plan is better or worse than the frozen-plan, my application's performance will usually be as expected. But there would be spikes (upward or downward) on my dashboards, and I'd be inclined to investigate those spikes. Depending on what I find, I would either change/update the frozen plan, or increase the percentage even higher, to reduce the number of spikes in performance.