Eventually, yes, I think we'll delegate to AI in more and more complete ways, but it's a process that takes some time.
Goes to the larger idea that strategic and logic is important for scalability and long term success. Not just execution. Something LLMs miss often (mostly because people fail to communicate it to them).
That being said, if you're writing in C, having a pretty good idea of how a cpu generally executes instructions is pretty key to success I'd say.
If you are a tiny startup, the marginal gains from these optimizations matter a lot less than if you are Netflix.