Tuning queries still makes plenty of sense. Slow/expensive queries need extra infrastructure like a memory cache server or more app servers to paper over the inefficiency.

That's a bunch of added effort that might as well be spent on understanding your database. Even something simple like using materialized views can significantly increase performance of expensive queries.