2 ms·
SQL separates query definition from implementation because there is a planning phase between them that can be sometimes quite complex. To choose the best path
by iTokio 2y ago
SQL separates query definition from implementation because there is a planning phase between them that can be sometimes quite complex.
To choose the best path to retrieve data, you have to know what are the possible paths (using the underlying data structures, indexes but also different algorithms to filter, join…), but you should also know some data metrics to evaluate if some shortcuts are worth it (a seq scan can be the best choice with a small table..).
And the thing that will trip most humans, is that you need to reevaluate the plan if the underlying assumptions change (data distribution has become something that you never expected).
Note that the planner is also NOT always right, it heavily relies on heuristics and data metrics that can be skewed or not up to date.
Some databases allow the use of hints to choose an index or a specific path.
- rtpg 2y agoMy honest experience in a skilled team has been that people more or less start off thinking "OK, what indices do we need to make this performant", work off of that, and then in the end try to have queries that hit those ones. I understand the value of full declarative planning with heuristics, but sometimes the query writers do in fact have a better understanding of the data that will go in. And beyond that, having consistent plans is actually better in some ideologies! Instead of a query suddenly changing tactics in a data- and time-dependent way, having consistent behavior at the planning phase means that you can apply general engineering maintenance tactics. Keep an eye on query perf, improve things that need to be improved... there are still the possibility of hitting absolutely nasty issues, but the fact that every SQL debugging session starts with "well we gotta run EXPLAIN first after the fact" is actually kind of odd!