4 ms·
Which plan is chosen has a dramatic impact on performance. Various stats tables give clues which plan will be best, but there will always be queries where the
by londons_explore 6y ago
Which plan is chosen has a dramatic impact on performance. Various stats tables give clues which plan will be best, but there will always be queries where the stats still don't lead to the optimal plan...
Therefore, I believe all query engines should allow the changing of a plan mid query. Ie., when a query or subquery is not producing matching rows as rapidly as predicted, the query should be paused, a new plan selected, and the query continued with the new plan.
My testing of this on a fork of postgres found some queries can see a 1000x speedup, especially ones with tens of joins.
This leads to some complexity around cursors, but in my view, this complexity is worth it.
- trhway 6y ago>the query should be paused, a new plan selected, and the query continued with the new plan. that is much more complex while in principle similar to the well known more practical approach - run a quick sampling of the related data and choose the plan according to the sampling results.