I haven't done this in GT5, outside of race mods (where there is an "agility bonus" from weight reduction, too), but in GT4 it did make quite a marked difference on its own. The first car I applied it to was the 400R, and you're right, you need to tweak the suspension to make the most of it. If you're not wanting to run the car with really stiff suspension, it does tend to "ruin" it.
Anyway, in theory, it's supposed to reduce chassis flex, which basically limits your practical / useful range of spring rates (why go stiffer than the chassis? No benefit, and potentially big welding bills). The effects are mostly on transients, e.g. corner entry / turn in etc. where the chassis flexes most before "winding up" to a higher stiffness. Another benefit is that chassis flex no longer affects suspension geometry as much, at least in the real world.
A common rigidity upgrade for cars with MacPherson struts is the strut (tower) bar / brace, which ties the towers together and reduces their flex relative to the chassis - making the suspension do more of the work in the process.
It's strange how marked yet subtle the effect is in-game, in terms of driving feel the car does feel much sharper; it shows that despite all the flaws, PD are doing some things right!