Technically, there is no such thing as a "primary ground", all grounding electrodes are part of the grounding electrode system.
However, your understanding that scientifically the water pipe has a way smaller resistance to ground than the ground rods do is correct also, it is a far more "effective" (notice the air quotes) grounding electrode. Some of them go for miles and are bonded to everyone else's ground rods etc, so they have a very good connection to the earth.
What the code says is that all electrodes present at a structure must be bonded together to form the grounding electrode system (250.50). If there is rebar in the footing, it must be bonded to the system. If there is metal water pipe in the earth, it must be bonded to the system. If random ground rods are driven around, they must be bonded to the system.
A lot of electrical contractors fail service replacement inspections because they drive two ground rods and think they are done, without bonding to the metal water pipe. That is not correct, if the pipe is there, you have to bond to it.
Obviously, water pipe electrodes need to be supplemented with ground rods or similar per NEC