First of all, I believe weight takes a back seat to balance. A cue balanced correctly will feel comfortable regardless of weight (within reason). That said, heavier cues were likely more prominent in days past because of playing conditions. Mud balls on nappy cloth required some power to move the ball. Aramith Pro balls on Simonis requires much less power to move the ball well. You don't need a powerful stroke, nor a heavy cue to get the ball rocketing around the table.
Another thing to consider is each players' stroke. Heavier cues tend to buffer a shaky or tense stroke, to smooth it out. I recall reading something Mizerak commented about having a heavier cue for days when he was shaky or tense. That always stuck with me. So really, weight is quite personal. It's one of the reasons I began using an adjustable weight system in my cues. I used to set the weight so that nobody could jack with the balance, but the guy who buys it secondary may not like the weight. Having adjustable weight sure makes things easier on the buyer.