Simple version: distilled water is good.
Complicated version: This actually depends on a lot of variables. Some places tap water is nearly as good as distilled. Some places, tap water is awful. Vegas comes to mind. I've never run into water with enough mineral content that it could block the system, but I'm from Vancouver, Canada, where we have some of the best natural water on the planet. Water that bad might very well exist, I just haven't seen it.
As mentioned above, correctly, rust in the coolant is caused by oxidation of the iron block by the oxygen in the water. Furthermore, as our coolant system is almost a sealed system, there will only be so much rust produced before all the oxygen is gone from the coolant. Same basic theory as boilers.
There's another factor as well, called galvanic corrosion, due to dissimilar metals in our cooling system mixed with water that is capable of carrying an electrical current. A bit of background here - pure water doesn't conduct. Water has to have impurities in it to carry a current. Therefore, using distilled water drops the level of impurities, making your coolant less conductive, thereby dropping the chances of galvanic corrosion. The antifreeze that you put in is designed to help with this as well.
Ya, I know, clear as mud.