A lot of it depends on what you have. The diesels cna gain a good bit of HP, but it invalidates the warranty, and as anytime you modify an engine to get more HP, it does put more stress on the engine. On a gas engine, the main thing it does is change the shift points, though it can increase the HP some.
I do have a tuner for my Ranger, that has 3 programs in it. an 87 octane performance, 87 torque, and 93 torque, for trailer towing. I can tell the difference in power for the 93 octane program, but it does require premium fuel when running it.