How does a bike's weight affect the overall experience of owning it?
Other than the obvious fact that your better quality (and better handling) bikes tend to be lighter, there's no real correlation between weight and performance (other than a modest effect on acceleration and the obvious effect on hill climbing).

But you can generally (with some exceptions) assume that a bike that is quite a bit (like 2x) heavier than others of the same basic style is poorly made, with poorer quality components.  Eg, hubs and rims are apt to be stamped steel rather than machined aluminum, the crank is likely a solid steel one (with large, crude bearings) vs a 3-piece one with alloy arms and better quality bearings.  The spokes are probably just plated steel rather than stainless.  The pedals are probably stamped steel, and when you spin them you can hear the bearings rattle.  And the tubes in the frame will be so thick and heavy that the bike will feel stiff and "dead".

That said, there's no need for a beginning cyclist to find the lightest possible bike.  Eg, a good quality steel frame will be maybe five pounds heavier than the equivalent aluminum one, but the steel frame will be cheaper and more durable.  Other components may be heaver or lighter as well, making possibly a 10-15 pound difference between two essentially identical bikes, but beyond a certain point lighter, more expensive components (eg, drilled brake levers, ultra-thin tires) are less durable than their slightly heavier counterparts.

Finally, if you're still tempted to spend an extra $300-3000 to get that ultralight bike, ask yourself how much you weigh, and whether you couldn't afford to lose a pound or two.  The heaviest thing, by far, on a bike is the rider.