The amount of back pressure produced by the performance exhaust system is crucial as too much back pressure will have a negative effect on your engine's top-end performance as it will restrict the flow rate of the exhaust gasses at high RPM. The result would be the engine not being able to expel the spent exhaust gasses fast enough to prevent spent exhaust gasses from contaminating the fresh air/fuel mixture that is drawn into the engine on the next intake stroke. Ultimately, this will result in reduced engine power!
Therefore, attaching a little 1-inch pea-shooter to your engine instead of an performance exhaust system is not such a good idea! But then neither is fitting a 10-inch sewage pipe. If the exhaust pipe is too large, you will get reduced flow velocity of the exhaust gasses. The flow velocity of the exhaust gasses assists with the scavenging of the spent exhaust gasses as well as the amount of air/fuel mixture that can be drawn into the combustion chamber on the next intake stroke. This is because the flow velocity of the exhaust creates a low pressure immediately behind it that sucks more gasses out of the combustion chamber. The trick is thus to get the exhaust back pressure just right.
Increased power from the engine due to exhaust tuning is often the main goal of the Tuner.
To produce power, you need to maximise the efficiency of the engine, which can include a greater turnover of air and fuel passing through. If you can get the exhaust gasses out easier, you can get more air and fuel into the engine to produce more power. Any backpressure will reduce the amount of air and fuel that can fill the cylinder and reduce the power. That would also point to the most powerful exhaust systems being entirely open from the exhaust valves, but that is not the best design.
When correctly designed, the modified exhaust can be tuned to resonate and the pressure vibrations in the tuned exhaust can be timed to have a low pressure wave arrive at an exhaust valve as it opens, pulling the gasses out. This tuning isn't something you can easily modify without advanced modeling of fluid dynamics but many performance systems are set up with this in mind.
The exhaust manifold is the major component responsible for exhaust tuning. The length and diameters of each pipe can be prepared to create a resonant effect over a certain RPM range. It is worth noting that unless the diameter or length changes, the resonant RPM band is fixed. When the engine is at the specific RPM range to match the exhaust frequency, the tuning comes into play. This effect will boost the torque at that RPM range although it may also reduce power at other RPMs.
When exhaust systems are quickly modified by adding different rear silencers, or larger diameter pipes, the tuning can be disrupted. In a similar fashion, you wouldn't cut the last metre from a Church organ and replace it with a larger pipe without expecting the sound to change!
It is often these changes that disrupt the resonance and tuning in an exhaust system that are noted for a reduce power output. Without understanding the resonance effect, often the incorrect conclusion that the free-flow exhaust has created the problem by being too free-flowing.
Performance Exhaust systems are therefore something that can not be easily designed. There are always compromises somewhere, with the first to go usually being the silencing effect. If you are looking at buying a new performance system, you should look out for changes to the design that would be beneficial. For example, updated equal-length exhaust manifold primary pipes, or repositioned X sections on "V" engine exhausts. Larger diameter pipes may change the sound, but it is often unlikely you will notice any improvements to the power. Likewise, Stainless steel exhaust manifolds that follow the design of the original will not provide any improvements as they will not fundamentally change the exhaust tuning.