Indices are standard way to achieve basic performance levels of a database. They may have their downsides but "potentially dangerous" is dramatically overstating the case. Furthermore, the dangers of premature optimization are about taking extra time or adding complexity to something that ultimately doesn't matter, not about using very basic features in a sane way.
The correct optimizations to make first are ones that make the biggest impact percentage-wise as well as being the most elegant in the code. Indices typically fit both categories very well. Unless you are doing a lot of stupid things, there's not going to be much lower-hanging fruit, but even if there is, after you apply sane indices that will be when your profiling will start to reveal the real interesting possibilities for optimization. The idea that indices are a good final optimization does not show much interest in real performance.