Different exhaust sizes actually affect the motor differently. Larger diameter/shorter in length exhaust can give you more hp while the opposite can give you more torque, to a point though. This doesn't mean you can stick a straw on for exhaust and get incredible amounts of torque. There are a lot of other factors that play into affect as well. too. For example, if you have a turbo charged engine, you might want to choose a smaller diameter exhaust. This may be a little more restrictive but the smaller diameter makes for higher exhaust velocities, causing the turbo to spool quicker.
"Why wouldn't you want the largest pipe possible on a NA engine?" You always want a little back pressure on the motor. You wouldn't want the largest pipe, having a completely unrestrictive exhaust on a motor can cause you to burn your valves.