"Retard" means "slow" in French. When the word came into common English use in the 20th century to describe mentally retarded people, it was offered as a kinder alternative to medieval words like "idiot" and "fool" and images of pitiful, sometimes dangerous, social rejects.
Officially, mentally retarded people have low IQs and trouble learning certain skills. "Retard" as an insult is a comment on someone's intelligence--usually sparked by something small, like not getting a joke or saying something corny. The implication is that you are not just "slow" or "behind" at that moment, but in a permanent sort of way.
The word "retard" also has a patronizing effect--a common complaint from mentally retarded people is that they're treated like children--and that same inferior feeling also carries over to the insult.
Used as a joke, on the other hand, the word "retard" has lost its sting to the point where it can mean the opposite of slow and stupid. The word is making appearances lately on a range of super-sarcastic websites, cartoons and fanzines.