I found this out shooting a few things at f22 on our trip. They were scenes I where I wanted to get as much in focus as possible. I didn't have the information in my brain yet about the affects of diffraction. My lenses are fairly new yet, so I haven't worked with them enough to know their "sweet spots". Does this vary more my copy of lens? Or is it fairly consistent between copies? So I guess the question is, can I ask others what is typical, or is it going to be different for each one?
IN GENERAL, for most wide angle shots you shouldn't need to go beyond F7.1 or F9 or so to get everything in focus. It's generally better in these types of shots to focus fairly close than to focus on the distant mountain.
Try out some DOF calculators like those you can find at
www.dofmaster.com. They should shed some light on what apertures you actually need.
IN GENERAL, again I wouldn't exceed F9 unless you are in macro range. There really isn't much that you'll need beyond this aperture to capture. On pretty much any resolution camera you will be in the diffraction range at this point.
The larger the MP in the same size sensor, the earlier the diffraction limit. This does not mean that a lower resolution sensor will capture more detail at higher apertures. It simply means you are not achieving the highest possible resolution of the sensor anymore.
In general for normal shooting I stay between F2.8 and F7.1. F2.8 would be for low light or portraits and F7.1 would be my landscapes. The shorter the focal length the lower the aperture you need to get everything in focus. So while F4 may be enough to get everything in focus at 17mm(35mm equiv) this may not be the case at 100mm.
The only times you really need higher apertures are:
Long exposures without a ND filter(sometimes diffraction is worth it for the ability to get a longer exposure...fireworks, water, etc)
Starburst effect(with rounded apertures this only occurs at F11 or higher typically)
Macro - Sometimes F11 or higher is necessary to get enough in focus. With the MP-E lens from Canon it can be used til F96 or so!
Those are about the only situations I can think of at the moment...maybe somebody will have some others.