This is a surprisingly complicated question. The simple answer you'll hear from almost everyone is that if it didn't feel good to your ancestors, they wouldn't have had sex and you wouldn't be here. But is that true?
I don't think so.
For a very long time, large parts of philosophy and psychology were dominated by a one-dimensional pleasure-pain model, the idea that we do things solely because, at some level, they give us pleasure or let us avoid pain. Although it sounds perfectly plausible, it becomes a tautology. If we do something that looks unpleasant, it must be because, somehow, in some deep recess of our minds, we must indirectly find it pleasurable or find not doing it even more painful. If we avoid a pleasure, it must be because in some indirect way we perceive avoidance as leading to greater pleasure or preventing greater pain.
The holes in this model are huge.
In the first place, study after study has shown people acting in ways in which pleasure and pain do not appear to be opposite ends of a single continuum. Pleasure does not appear to equal negative pain any more than pain equals negative pleasure. Instead, our choices reveal deeply non-transitive preferences, in which odd mixtures of pleasure and pain, and of gain and loss, win out when they shouldn't in any simple arithmetical sense.
This is part of why the old classical models in economics and political science have taken such a beating recently. Both were built around theories based on strictly linear preference functions, whether called "utility" or "value" or something else, and both assumed that it was irrational to prefer C>A if you prefer A>B and B>C.
Unfortunately, the real world abounds in scissors-paper-rock intransitivities and even weirder situations where the right amount of pain is good and too much pleasure is bad.
Motivation: Pleasure/pain? or compulsion?In the second place, biology and psychology have provided us with a wealth of examples where motivation seems to be completely unrelated to either pleasure or pain. Observe someone with OCD washing his hands for the 20th time. Ask him if it is pleasurable, and he'll deny it adamantly. He hates it, he hates not being able to stop it, and he gets no pleasure from the action. Ask him if he feels pain when he resists the compulsion, and again he will say no.
The sense of compulsion may be extremely hard to resist, but it does not feel at all like holding a hand over an open flame. Resisting the compulsion takes effort, and giving in feels like yielding to exhaustion, not pain.
As we accumulate information about neurotransmitters and circuitry in the brain, we are piecing together a very different, more complicated picture of motivation. Watch this 5 minute snippet from Robert Sapolsky's lecture on dopamine:
Dopamine Jackpot! Sapolsky on the Science of Pleasure
Consider a long-term compulsive gambler pulling the arm of a slot machine over and over to the point of collapse. You would think that there would be a certain amount of pleasure involved on the rare occasions when she wins, but no, she mostly just looks tired and blank as she scoops the cascade of coins into her bucket so she can feed them back into the machine. It's not winning anymore, it's just a random event that is part of the process of feeding the compulsion.
What's happening is a person being subjected to a carefully calibrated process of manipulating dopamine, which, as Sapolsky describes so clearly, spikes before (often long before) any reward occurs. In fact, dopamine also spikes in the same way when you play a game that punishes you when you lose and does nothing when you win, so it has nothing necessarily to do with pleasure. It goes up a bit at the moment when you think you understand what's going on, but it really spikes with uncertainty, and that's what makes gambling so hard to resist.
And dopamine is the real motivator. Block the dopamine and you block purposeful behavior. People still enjoy things that are pleasurable, but with dopamine blocked they won't create a plan or perform actions to receive them. Furthermore, dopamine peaks get smaller as control and predictability go up, so predictable pleasures become less motivating and predictable pain becomes less aversive, creating one big element of intransitivity.
The converse is also true. Increase the dopamine level and you dramatically increase compulsive behavior. Parkinson's patients need L-dopa, a dopamine analog, to treat their disease and restore normal function. But many Parkinson's patients are forced to do without it because when they take it, they become compulsive gamblers, and their gambling wrecks their lives, their families, and their finances. Take away the drug and the Parkinson's symptoms return, but the gambling compulsion stops. Ask if the gambling was fun and they ask you if you're crazy. They hated it!
Deep compulsions aren't funThe picture that emerges is one in which pleasure and pain both signal that something is important and should be the focus of your attention while you figure out what is causing each one. Getting more of the good or less of the bad is clearly a motivation, but it may be a very weak motivation compared with the drive to understand why the good or bad thing happened and how to control it.
We often prod and poke and risk the repeat of unpleasant or even painful stimuli in our need to understand what happened and why, and even if we feel a little bit of satisfaction when we succeed, it is often not at all comparable to the pain or discomfort we experienced. Yet we persist, like the gambler, to try to pin down and coerce the unknown.
Doing things under compulsion isn't fun and, from an evolutionary point of view, things don't need to be fun if biology is going to compel us to do them anyway. Watch a female cat while she mates. There is no way that process appears to be anything but painful and unpleasant. As she goes into heat, she screams and howls and acts like she's in great distress. She snarls and spits at her suitors when they appear. She finally submits and is mounted, and then she screams in real pain as the spines on the tom's penis rip her vagina as he pulls out. You know your cat well. You know when she's happy, angry, curious, fearful, upset. THIS IS NOT WHAT PLEASURE LOOKS LIKE.
In short, many things besides pain and pleasure are involved in our fundamental motivations. Reproduction is a drive in and of itself. Sex does not have to be fun for an organism to be motivated, even driven, to have sex. Hormones are perfectly capable of driving behavior, and they do not need to be mediated by sensations of pleasure or pain.
So why is human sex pleasurable?
Pain is relatively straightforward. It's an aversive signal and an attention-grabber, and that's true for all species complex enough to feel what we would call pain. Pleasure is a different story and the capacity for pleasure seems to vary much more widely from species to species.
For that matter, why is anything pleasurable?
I think, from an evolutionary point of view, that pleasure is like money: an accounting system that allows us much greater flexibility in our behavior. Things that are enjoyable can be sorted and weighed and balanced against each other. Pleasure lets us plan and trade, more of this, less of that, or maybe give up all of these in exchange for a whole lot of some other thing. It gives us a common currency for deciding whether to trade a toy for social approbation, or deciding whether to refrain from eating one marshmallow now in order to have two marshmallows in 15 minutes.
And here's the point of this long-winded excursion: pleasure is evolution's motivation for doing things that are generally beneficial but situationally optional.
If something is a fundamental non-negotiable demand, evolution will equip a species with an unmistakable drive. If it's usually a good thing, but not always, it's likely to be fun. We get pleasure from satisfying inclinations, not from complying with compulsions. As a general rule, if you can't say no, saying yes isn't fun. It's just something you have to do.
The crucial thing is that we can maximize pleasure in a variety of ways. We have choices. We can also adapt and personalize our ways of finding pleasure, allowing much more individual variation, which is important in a highly cooperative species where we need individuals to take on a wide variety of roles.
Furthermore, the non-mandatory nature of pleasure lets us adapt and survive in situations where pleasure is scarce. If we are raised in a very Puritanical culture, in which even the appearance of pleasure-seeking is punished, we can survive with very little pleasure in our lives. Pleasure means that something is usually a good thing, but that it's not absolutely essential.
So why is sex pleasurable for humans, but not for cats?If the preceding argument is correct, the answer should be obvious: sex is solely about reproduction for cats. From an evolutionary point of view, it's mandatory, so it doesn't have to be fun. But sex for humans is about a tremendous amount of other stuff besides reproduction. 99% of the time it's only about social interaction and pair-bonding, because the female isn't even fertile.
And since humans live in very complex social systems, with wildly varying customs, the rules about when to have sex and with whom are much too complex to be left to a blind drive. So sex is fun, because A) we need to be strongly motivated to have sex to establish and maintain bonds between individuals, but B) it has to be optional, because doing it at the wrong time in the wrong place or with the wrong person can be destructive of the same social bonds.
We still have an underlying drive to reproduce, but it is channelled and controlled by our oversized forebrain. Although you get hints of the latent strength of this drive when alcohol or drugs diminish the forebrain's control, you only see it clearly when a person has severe damage to the areas of the brain involved in damping it, and as a result the person has to be physically restrained from trying to have sex with any available target.
But in healthy individuals, the underlying sex drive usually expresses itself in a drive to find partners to mate with within acceptable rules. Furthermore, the pleasure that happens during the initial matings can be quite limited without diminishing the urge, because pleasure is not the primary motivation. It's just a bonus.
Pleasure really only becomes an important motivation once access to sex is secure. If you can have sex whenever you want, why bother? Because it's fun - or at least it can be. But it's optional. Working late, got to get up early to go fishing? No sex. Nothing better to do? Let's get it on!
If it's not fun for both parties, it tends to dwindle to the level of the basic drive, which is greatly weakened by familiarity - the famous Coolidge effect.
In this sense, the human capacity for enjoyment of sex is the flipside of covert ovulation. Both occurred to keep males strongly attached to females by making sex frequent and enjoyable, but optional, instead of rare and mandatory.
Under the right circumstances, both cuddling and slow, sustained sex are powerful triggers for the release of oxytocin, the pair-bonding chemical, so at the neurochemical level having good sex helps set up the bond and helps maintain it. If men didn't enjoy sex, they wouldn't have it enough to sustain the bond, so they wouldn't hang around and help with the kids. And if women didn't enjoy it, they wouldn't offer it to the males enough to keep them happy and to sustain the bond.
Why smart, social species have more funSolitary species are mostly compelled by biological drives. Optional pleasures play a smaller role and are often pretty much confined to childhood and parenting, the most social parts of their lifecycle.
Social species, on the other hand, have a wider mix of biological drives and pleasure rewards, because they need the flexibility to change behavior and roles as needed to fit into a group that changes over time. Intelligent species need even more flexibility to allow for learning and culture, so biological drives need to be subordinated and pleasure becomes much more important as a reward system.
Humans, as the smartest and most social mammals, need flexibility the most. As a consequence, we have less specific and more indirect innate drives than any other species, so pleasure is more important for us as a currency for choosing mixed goals and optimizing our behavior in widely differing environments. Thus, in a sense, the evolutionary reason we have so much capacity for pure enjoyment in so many things, not just sex, is that it necessarily goes along with being smart and social.
Note that dolphins are a close second in terms of intelligence and sociability, and they are also famous for appearing to do many things (including sex and masturbation) just for fun. Also, pleasure and fun play a much bigger role for most species during childhood, when learning and flexibility are more important.
On the other hand, intelligence, sociality, and the need for flexibility don't mean that pleasure and pain are the whole story for humans, or even the biggest part of the story behind motivation. We still have powerful drives that can override both pleasure and pain, as addictive and compulsive behavior clearly demonstrates.