A good trigger job will smooth out any stacking or creep it might have, will generally lighten it (though to different weights depending on if you're going for a competition gun of a defense gun), and will generally smooth out the three parts (not literal parts) of a trigger pull: the pretravel (where you can get creepy or stacking triggers) the actual pull and reset, and the overtravel (the movement of the trigger after the sear release).
Well, anyway, to answer your question, definitely not limited to competition pistols. Whenever you see a high-end defensive pistol, it's had a trigger job.
Generally they say you don't want less than a 4lb trigger pull on a carry gun, but there are definitely trigger jobs that improve the trigger for a carry gun. For a competition gun some people go down to around 2lb.
A forum community dedicated to Springfield Armory XD and XD-M series firearm owners and enthusiasts. Come join the discussion about optics, gunsmithing, styles, reviews, accessories, classifieds, and more!