Yup, shaving 5-10 cycles from compiler optimizations doesn't help you when you've got a deficit of 200+ cycles per cache miss.
Working with FPGAs and DRAM really makes a lot of reasons behind this and lot of other "obscure" performance issues like pipelining crystal clear.
Acton (sort of) has a talk about that too. Except he speaks about traditional, ahead-of-time compilers. Tldr is that because of how the vast majority of programmers arrange their data, CPUs spends the vast majority of their time stalling on memory. Therefore improvements in optimizing compilers can only marginally help the marginally small section of running time spent actually exercising the ALUs. The solution is to devote more effort to changing our habits regarding data layouts.