In some instances, when you notice strange WP spikes and quite a few 'time critical DB' calls, which some are normal, but there are a lot more than normal, then bad indexes could be the issue.
You cannot generalize to say that index rebuilding for performance is rare, or even medium rare, it depends on many factors, most importantly the characteristics of the application.
- In scientific applications (clinical, laboratory) where large datasets are added and removed, the need to rebuild indexes is "common".
- Conversely, in system that never update or delete rows, index rebuilding rarely improves performance.
- In systems that do batch DML jobs, index rebuilding "often" improves SQL performance.