Instead the trend mostly seems to be that we do it by hand until automation becomes good enough to do it better than we were doing it, and then we move on to harder kinds of problems and let automation do its thing.
I say "mostly seems to be" because there are plenty of counter-examples. But that's the general trend. As is seen by the explosion of programmers working in what used to be considered unbearably slow scripting languages, and the relative lack of people doing traditional assembly programming.
This strongly depends on the scenario.
If people are identifying and optimizing a hot spot, it tends to happen this way, yes.
But what I am thinking about is the common situation where a company produces a product in a low level language, and then later on a competitor produces a competing product in a high level language. The second entrant is optimized by an automated process, the first was done by hand, and can't easily be rewritten.
In these situations - and I have encountered several - the second implementation frequently winds up doing more and doing it faster than the first implementation.