When a tap is opened, water is released from the pipes, so the water pressure drops a bit. When you close the tap, the water pressure increases again. As the water flows into all the pipes where the pressure dropped, water pressure increases as fast as a valve or restriction permits the necessary amount of water to enter the system. The whistle comes from the location of that restriction or valve as the water speeds up through that restriction. High speed water actually makes a vibration as it passes through, somewhat like air passing over a reed in a musical instrument. When the system is full, the water has stopped flowing into the system, so there is no high pitched sound.