The frustrating thing about such optimizations is how badly they fit with the rest of the software development and testing workflow. Ideally, you want to write

The frustrating thing about such optimizations is how badly they fit with the rest of the software development and testing workflow. Ideally, you want to write a test that prevents a performance regression, but then you need to set up a test database with the right data characteristics (but not real data, for privacy concerns), need the hardware to handle that, need to maintain that database through future schema changes and so on. It would also be nice to have some kind of mechanism that can tell you that the optimization is no longer necessary when you switch to a new DB version with an improved optimizer. Most organizations don't have the engineering capabilities or capacities to provide this kind of tooling and infrastructure, so the benefits of CI/CD often don't apply to such changes.