I wrote my thesis on this! Application-specific system design can get you orders of magnitude performance improvement, as well as better scalability/fault tolerance properties. I focused on graph analytics, but it's reasonable to think it applies more broadly.

Definitely true that application-specific design is often not worth the investment though. Chasing that 1000x improvement can easily cost you a year or two.