The most basic problem in cold weather is that both the ground and the air are excellent heat sinks, in other words, heat will tend to conduct away from your warm body and into the ground or the air until their temperatures are equal, which they will never be, because the earth and air are much too big for you to warm up, so the reality is that your body must constantly resist cooling down to their temperature. Insulation doesn't stop this cooling process; it only slows it down to what is (you hope) an acceptable level that your body heat can keep up with.

Compared to the air in your mattress, the foam insulation in a foam pad is a very poor conductor of heat. An air mattress with no insulation puts very little conductive resistance between you and the air inside the mattress. The air in the mattress will quickly and efficiently conduct whatever bit of heat it contains directly into the ground, so no matter how much heat goes from your body into the air mattress, it will soon be conducted away, almost as rapidly as if you were sleeping directly on the ground.

The problem with thinking in terms of a few "cold spots" is that those cold spots are not static things. They are places where heat is actively being sucked out of your body. Your body will attempt to cut this loss by reducing blood flow to those spots, but there is a limit to that, since those cells need blood to stay alive. Eventually, your body will fight the cold loss by forcing you to exercise and burn calories more quickly to generate heat faster. That exercise is called "shivering".