You could make the same argument that we all should learn assembly language, because when our C compilers produce bad assembly we'll have to rewrite it by hand. But I haven't written assembly in 30 years, and even then it was just for fun.
As it turns out, having high-level abstractions means that in many ways it's easier to do automatic optimization. I look forward to automatic data-driven database tuning and query optimization.
Postgres has done an incredible job optimizing both the overall performance of queries and the query planner - but these things make mistakes still and being able to fix those mistakes can make the difference between a two minute query and a twenty milisecond query - this comes with the fact that complex database operations can usually make or break overall response times, and these optimizations usually depend on statistic accuracy which can be hard to ensure.
If SQL optimization was as good as compilers I'd be all on this train, but I just don't think we're there yet.
Your argument seems to assume that SQL is assembly language; it is not! The query plan or internal bytecode is the 'assembly language' of an RDMS, therefore SQL is more akin to the RDMS version of C, rather than assembly language.
So Malloy is akin to a language that compiles to C, which is then compiled to assembly. Does this seem like a worthwhile pursuit to you?
Personally, I don't think any SQL replacement will really take off unless someone can get the major RDMS makers to support it natively.
Just to be clear, I'm not saying it's not a worthwhile pursuit: SQL does have many problems and could do with a replacement that supports more modern approaches to problem solving. What I am saying is that unless someone comes up with something that can automatically do performance tuning for you, or that is supported natively by the RDMS, then SQL replacements will continue to fail to gain traction.
People do this, e.g. building a cost-based optimizer for queries, but it can take over 2 years for it to really get pretty good. And even then it can only go so far and you'll still want to manually tune certain queries.
As it turns out, having high-level abstractions means that in many ways it's easier to do automatic optimization. I look forward to automatic data-driven database tuning and query optimization.