I love SQL, but I strongly agree with this. It would be great to have a platform-specific low-level API for performance-critical queries. (Much like “unsafe” code in a managed programming language.)
I think the best analogy would be SQL is to OpenGL what this API would be to Vulkan.
Ability to define the query procedurally or atleast semi-so vs the declarative approach of SQL would allow direct optimisation of very performance critical queries.
I disagree. The amount of optimization that a query planner does is vastly more than a web application developer would care for.
We have people who can't be bothered to close database transactions (see that Uber report). I wouldn't trust then to choose the optimal join strategy in a complex query.
