My primary interest is self-defense, so that colors my perspective.

In general, I prefer bullet weights that are medium-to-heavy for caliber, within the standard range. So I like 130-158gr for .38 Special, 147gr for 9mm, and 230gr for .45ACP. I have no interest in ultra-light or ultra-heavy bullets. As you said, there does seem to come a point of diminishing returns when going to one extreme or the other. And especially in semi-autos, that could potentially affect functioning.

Just my opinion.