To my understanding, the best way of defending against a cavalry charge is with a wall of pikes. In addition, when it came to push-on-pike engagements, if one side had longer weapons they would generally win a decisive victory. Contemporary military expert George Silver even wrote that the pike was even one of the best weapons for one-on-one combat. So it's not surprising that by the 17th century, basically all of the European infantrymen who specialized in hand to hand combat were pikemen.
So why was it so rare for pikes to be used in medieval and ancient warfare? Some of them had massed spear formations, but rarely were they using the 18ft monstrosity that arrived later. Also, what about armies in Asia, why didn't they rely on pike formations?
I think it's important to note that the transition to pikes began in the late medieval/early modern period, so something like the 15th-16th century. Secondly, pikes WERE used in ancient times, quite extensively. The pike phalanx, pioneered by Phillip of Macedon, was the dominant form of warfare in the Mediterranean for at least a century (Between the campaigns of Alexander and the Macedonian wars) and was used for much longer than that. I have also heard (But am not much of a scholar of ancient Egypt and therefore cannot hard confirm) that the Egyptians separately developed pike formations on their own.
Certainly, you are correct, pike formations are effective against Cavalry (and infantry, in many cases) but they have substantial drawbacks:
The size of long pikes more or less precludes the carrying of large shields, leaving pike bearers more vulnerable to missiles. (Note that pikes became more relevant as shields became less useful) The Macedonian phalangites carried small shields, and most of the late medieval pike formations carried no shields at all. This leaves pike bearers vulnerable to missiles and any enemy that can close to grips with them.
Pike formations suffer over terrain that isn't flat and even. This is one of the main ways the Romans were able to dismantle the Macedonian phalanx. If a pike formation can't keep level and even, there will be breaks in the line enemy infantry can exploit. Because of point one, and also because their primary weapon is the pike, they will often fare poorly once the enemy exploits these opportunities to close.
Poor discipline exacerbates these problems to the extreme, and causes new ones. A poorly trained pike formation is nigh useless for anything but standing still and defending. Moving in the neat, even lines required for an effective pike formation is impossible without drill, setting aside turning or any sort of maneuver. In an early medieval context, where infantry was often levied and rarely if ever had any ability to drill together (setting aside urban militias) pike formations could be rendered deeply ineffective if they couldn't sit still and hold their lines.
Despite all this, pikes did see relatively frequent use, throughout every period of history I know, as did shorter spears. In fact, you'd be harder pressed to find a period where either the spear or the pike wasn't the primary infantry weapon than one where it was. (Barring the age of gunpowder and later) In fact, contrary to what fantasy films would have us believe, spears were probably more common throughout history than swords.
So pikes did see use, but they aren't objectively the best weapon or anything like that. Pikes, like almost any weapon, can be both devastatingly effective and pathetically ineffective, depending on the context, the enemy, the skill of the bearers and the tactics involved.
Check out the Battle of Cynoscephalae for an example of a pike phalanx being dismantled and any of the campaigns of Alexander or the early modern general of your choice (I'm weaker in that area) to see them used effectively.