I think waste is much more likely in startup and private sector. Where scaling up and out is easy and "cheap"...
The end result mostly depends on the balance between scientists and engineers in the development team, it will oscillates between "this is python because the scientists working on the code know only that but we are using MPI to at least use several cores" and "we have a direct line with the hardware vendors in order to help us write the best software possible for this thing".
For something like climate simulations where a project is running big long jobs repeatedly I imagine they spend quite a bit of time on making it fast.
For something like detector development where you run the hardware simulation production once and then spend three years trying to find the best way to reconstruct events less effort is put into making it fast. Saving two months from a six months job you run once isn't worth it if you have to spend more than a few weeks optimising it, and as these type of jobs need to write a lot to disk there's a limit to how much you'll get from optimising the hot loop.
In addition, the performance engineers tend to be employed by the facilities, not the computational scientists. They're the ones who do a bunch of legwork of profiling the existing code on their new platform, and figuring out how to squeeze any machine-specific performance out of the code.
A lot of these codes are time-marching PDE solvers that do a bunch of matrix math to advance the simulation, so the kernel of the code is responsible for a vast majority of the time spent during a job. So it's not necessarily a huge chunk of code that needs to be tuned to wring better performance out of the machine.
The parallel communication they do is also to an API, not an ABI - the supercomputing vendors drop in the optimizations in the build of the library for their machine, to take advantage of network-specific optimizations for various communications patterns. If you express your code in the most-specific function (doing a collective all-to-all explicitly, say, rather than building your own all-to-all out of the point-to-point primitive) the MPI build can insert optimized code for those cases.
There's some misalignment because the facility will be in the top 500 for a few years, while the code lives on and on and on. If your supercomputer architecture is really out of left field (https://en.wikipedia.org/wiki/Roadrunner_(supercomputer)) it's not going to be super worth it for people to try to run on it without porting support from the facility.