I would go further and say that there should be an alternate language that lets you just provide a planned path.
Just yesterday i spent time debugging a whole query thing cuz heuristics made the planner plan the wrong thing.
The planner is pretty amazing black magic that can just make life easy (just like compilers do a bunch of cool stuff.
But I think SQL perf would be a lot easier for people to grok if the low level involved specifying “hey, system, go through this btree, pull these items, merge with items from this other btree, etc”
Just yesterday i spent time debugging a whole query thing cuz heuristics made the planner plan the wrong thing.
The planner is pretty amazing black magic that can just make life easy (just like compilers do a bunch of cool stuff.
But I think SQL perf would be a lot easier for people to grok if the low level involved specifying “hey, system, go through this btree, pull these items, merge with items from this other btree, etc”