Cavalry charges are always frequently shown as terrifying in general history books, movies, TV,
video games, and fantasy novels. Even accurate historical accounts mentions the ground having an earthquake and things moving in slow motion as you stand with your legs shaking but stuck still on the ground due to fear.
However I borrowed a book from the library today on Medieval Warfare, and on the Battle of Hasting it described the Norman Knights charges against the
Anglo-Saxon shieldwall as something so terrifying that the Norman knights "displayed a most legendary courage very rarely seen in the early Medieval battlefield" and mentions several times how the Norman knights almost routed.
In addition the book has some battles during the fall of the Roman Empire and the years following it where the last of the Roman Equites and Patricians fought against impossible odds that would have "made brave men flee" as they made desperate attempts to fend off Germanic tribes using their cavalry or to hold onto far away territory. It mentions in Britannia how typical Roman cavalry would hesitate to charge even disorganized Celtic warbands wandering the countryside especially in forests and swamps and it took the Equites, the most elite of the Roman Army's horsemen and often coming from Rome's aristocracy, to be able to hunt down these disorganized local bandits.
And of course the book praises the Germanic horse warriors in its Rome sections especially after the final Sack of Rome where it was the horsewarriors of the Barbarians who would be the "hammer" of the Catholic Church as it was bringing stability into Europe during the Dark Ages. Especially the Frankish heavy cavalry who would become the basis of the Medieval Knight and the book mentions the Catholic Church's honoring the Frankish horse warriors as the "bravest" of the Church's military and who often took the most difficult and scariest tasks of guarding the Church's laymen throughout Europe.
I think some of the answer lies within the examples you cited. Meaning that you're talking about instances from several different eras. The level of experience, training, equipment and the horseflesh between each battle varied quite a bit. I think that some historians have made the mistake of lumping every century of cavalry warfare together in this monolithic mass and assuming that they can successfully draw conclusions from that.
While I hate to conflate modern warfare with that of the Middle Ages, I think the following analogy will work in this instance. To draw conclusions about medieval warfare based on say, the cavalry doctrine of the Roman Equites or how that arm was employed in Napoleon's day would be a bit like trying to examine Desert Storm through the lens of a WWI-era understanding of how tanks work. There's a lineage and some parallels to be sure, but the way a modern army uses an Abrams or a Challenger 2 is a bit different that how a Renault or Mark V would have been employed. And the capabilities are as different as night and day. A French Gens 'D Arme of the Compangnies 'D Ordannance during the Italian Wars looks very different and was far more dangerous than one of William of Normandy's Chevaliers for example. To continue the analogy, no two armies ever fight the same or the same way every time. The Russians and the US have very different ideas of what a tank should look like for example.
What I'm trying to say is that we need to examine these separate eras within their specific context, rather than making an assumption based off one specific era or battle and then applying it to the entire history of cavalry warfare. I believe that something similar occurred with John Keegan's work in regard to the Napoleonic Wars (and it was a conclusion largely based off the one battle out the entire series of wars as well). This conclusion was then propagated by other historians and in works of fiction by authors like Cornwall. However, we're talking about centuries of warfare and technical evolution in terms of equipment, training, tactics and horse breeding. In short, to present a piece of history that long and complicated is vastly overly simplistic. Warfare is more than just, "A will always triumph over B if A does C."
Now, I understand that an animal's basic nature can remain essentially unchanged. We do need to consider the kind of horse being used and its training/conditioning, though. Xenophon had different ideas about what made a good horse versus what a medieval knight would have ridden. A gelding ridden by a pistoleer in the 30 Years War was a far cry from the one ridden by a Gens 'D Arme in earlier times. A working horse in rural America is not the same kind of animal used in wars prior. In terms of its training, that is. A medieval war stallion, used in combat, was breed and trained from the time it was a foal for battle. Even today, stallions are still noted for being highly aggressive. Surviving illuminations and manuals show them being trained to do things like strike with alternating hooves, spin while kicking out, going up in the air and lashing out with all four legs at the same and to bite.
To go back to what I said about considering the specific type of cavalry within the context of its era and the technology available at the time, we need to consider a key factor. Medieval warfare encompasses almost a thousand years of history. A fully armored Gen 'D Arme on a fully armored horse from the Italian Wars looks quite a bit different than his ancestor from the time of say, Crecy. During the medieval period, siege warfare was the predominant form of combat. Battles were rarely fought and often avoided. Battles can be decisive at best (which means someone is always going to come off the worse for it and there's no guarantee it won't be your side) and a stalemate at worst. Sieges were usually the safer bet for a commander. So that means that there would have been quite a bit of action in terms of guerilla operations, raids, sabotage, foraging, reconnaissance and ambushes. All which are actions in which a heavily armed and highly trained mounted force can shine at the small unit level. The Medieval Man-At-Arm's ability to fight at the small and large unit level, his high amount of training, his ability to afford good equipment and his training to fight on horseback and on foot was what ultimately made him so valuable in war. It all boils down to his innate versatility, basically.
Another thing to consider is that when battles did occur, the intended purpose of the heavy cavalry was to go and sweep the other guy's cavalry off the field and then maneuver on the now exposed heavy infantry while their attention is taken up by friendly infantry. Basically, medieval commanders appreciated the value of combined-arms tactics and understood that each arm is supposed to complement the other and help mitigate their weaknesses and maximize their strengths. Just like any other era of warfare really.
I realize that was probably a very long-winded response, but I say all that provide a context for my answer to the core of your question. That being the idea of the cavalry against the infantry. The answer is that it largely depends. I hope that all I typed above illustrates how important tactics, doctrine, institutional knowledge, training, equipment, leadership, morale, technology and the actual horseflesh are when considering this. Another factor is that no intelligent commander, regardless of his branch, wants to charge home against the frontal defenses of an enemy if at all avoidable. Regardless of the army, the doctrine, type of unit or the era, the general consensus has always been that maneuvering around an enemy to hit their flank or rear is generally the safer approach. Units don't operate in a vacuum and you don't have to be that intelligent to figure out that supporting an attack with every possible enabler is usually the best option.
Now, there are examples from multiple eras when the cavalry was repulsed by the infantry and there are examples when the infantry was broken. In fact, the preferred tactic of Men-At-Arms against infantry was to charge, break their lances and then wheel away to go get more lances, change horses if need be and then return to charge again. One thing that I think people don't necessarily consider is that just because the cavalry charged home and slammed into the ranks of the enemy foot does not necessarily guarantee a victory in and of itself. Now, I realize that horse can generate a lot of impact. More so one from the Middle Ages with barding and its armored rider. However, the heavy infantryman (prior to the age of gunpowder warfare) was generally well-protected and had the press of the reinforcing ranks of his brethren to help him hold his spot in the ranks. Just because a formation ripples, flexes or gives in places does not mean that it is broken.
Two prime examples of this are Nancy in 1477 and Ravenna in 1512. The Burgundians charged home against the Swiss pikes and even the Swiss grudgingly admitted their admiration for the Burgundians' bravery and said that they manage to penetrate as far as the banners. Now both sides took heavy losses, but the Burgundian Men-At-Arms were defeated once their charge was finally stalled and the Burgundian leadership failed and their army was defeated. Ravenna is another example of where the French charged home against the pikes and actually punched through and then went on to defeat the Italian Men-At-Arms behind the pikes and then took up the Italians' discarded lances and charged the pikes again. Both sides displayed incredible determination and courage, but neither one was able to gain the upper hand until the pikes saw French-friendly reinforcements arriving on their rear and surrendered.
Moving on to the psychological aspect of such a clash …
Imagine if you will, it's the time of the Italian Wars that I've referenced so many times. This is the heyday of the heavy cavalryman, never has he been so well-armored or his prized horse so well protected. You're a Pikeman, marching with your triumphant brethren through the smoke and wreckage of earlier clashes. All the sudden the music changes and the officers begin to bellow new commands with that calm, but urgent tone of experienced men who know they're about to look Hell in the face again. As one, you and your fellow veteran pikemen march to a halt and hastily dress the line.
You ready your pike and scan your cramped surroundings, your view hampered by the armored bulk and the garish outfits of the soldiers around you.
Then you feel it before you hear it, that deep steady rumble that seems to emanate from the ground and up through your feet. You hear jubilant cries in the tongue of the accursed French and you see the grim expressions some of the older veterans and a few of them swear softly before being cursed back to silence by the officers. The faint sound of jingling harness, clinking steel plates and the hoofbeats of heavy horses draws nearer and you see them crest the small rise in the bloody field before you. Every of them one likes inhuman in their bright steel and fine cloth. Their horses are be-decked in steel plates and brightly-colored caparisons. They look something from the mind of some demented hellish painter. From their golden spurs, to their brightly enameled armor, to the rainbow of colors in the magnificent plumes atop their beaky-looking helms, they are a sight to behold. High trained and fully confident in their abilities to the point of arrogance, these are the epitome of mounted chivalry, the French King's mailed fist, his Gentlemen-at-Arms.
The sun flashes off their fine plate and the rich fabric of the cloth over their armor. What catches your attention, though, are the gleaming points of the eighteen-foot lances that every single one of them holds. It looks as though a small forest, with streaming pennants, has uprooted and is surging towards you. You feel your hands tighten on the grip of your weapon and your heart begins to beat so fast it feels like it's about to explode through your chest in a shower of blood. You send a silent prayer up to the Lord as the rattle of the harquebusiers' weapons echoes around the formation as they present their guns and await the command to fire. Your own officers soon pass the command to make ready and you lower your pike without conscious thought, bracing it just like your Father taught you.
The sound of music and barked commands is drowned out in the roar of Harquebus fire and a thick grey fog of biting smoke soon rises between you the oncoming cavalry. The ground now to seems to shake beneath your feet like something from the Old Testament.
Then … the French emerge from the cloying smoke in a single wave of steel and horses. As one they touch gilded spurs to their horses' sides. As one these arrogant warriors lower their lances and they break into a headlong gallop with clods of wet earth flying up from beneath their steeds' iron-shod hooves. You know, you know full well that fleeing will just mean your death. But even so, the animal part of your brain is gibbering with terror and wants only to run. You force yourself to focus and hope your training will carry you through … and that the men next to you will do their damn job.
The entire world turns into a blur of screams, a shower of splintered wood and the surging clash of steel. It sounds like the whole world is drowning in a tide of blood, screams, broken blades, the sound of steel on steel and the godawful dying screams of man and beast.
Ultimately, I hope that what I provided can show that it largely depends on the situation and the era. History has shown us examples where cavalry broke the enemy infantry, where the infantry held off the enemy horse and instances where the cavalry charged home and dealt out heavy casualties to the enemy but didn't break them. I hope that my post shows just how complicated the study of warfare can be and the situation as not as simplistic and monolithic as it's often presented to be. I'd also like to apologize for this mini-novel I just wrote you.