As a gentoo user, I have
a lot of experience with custom compiled software. My opinion is that for the vast majority of applications --and this should be held to be even more true for desktop applications-- compile optimizations have no noticeable affect on the user's experience. The only exception to this is prelinking, which does make a noticeable difference on loading times when an application is dynamically linked to a lot of libraries.
There are scientific programs, and other highly CPU-sensitive-in-one-way-or-another programs (eg maybe with -Os (compiled for size not performance) it will fit entirely in the cache), for which it can make a real difference, but generally it's not noticeable.
The one application that I've personally noticed it on is
MPD which I use to play music on random old boxes that I scatter through my home. It's a geek's way of creating an integrated, house-wide, home audio system. With proper compile time optimizations MPD will run on
really old stuff.