During the napoleonic war a square of british infantry failed because a single frenchman managed to get his horse to run suicidally onto the bayonets. It died (as, I don't doubt, did he), but it ploughed right through the formation in the process of dying and let the cavalry behind it get in amongst the redcoats.
A good example - of course I'm sure that it did happen that a brave (or mad) rush will destroy a formation, but during Waterloo, Marshall Ney repeatedly tried to break the Wellingtons army with cavalry charges and failed miserably. I think they only managed to break one square/regiment. Every other time the British and allies held quite contently.
But William the Conquerers knights did not break the Saxon shield wall at Hastings, nor did the English knights do much at all against William Wallaces pikes at Falkirk (English archers got that win...) and so on. I still can't think of any major battle where a calvalry charge actually did anything signficant to entrenched and determined infantry (But I'm sure there must be a few out there, just not many compared to all the failures...)
Doesn't it come down to a big game of chicken?
A reasonable way of putting it, but humans have the advantage in knowing that if they stand firm they have a very good chance of beating back an attack, and experience and common sense must apply - if you start running with an enemy on horse behind you, you are much more likely to get cut down.
The fear of the cavalryman as well should be thought of too - what if he knows that his side is not really putting their heart into it and it looks like his charge momentum is going to get him in the midst of a mass of enemy all alone and unsupported and likely to be pulled down and killed, then he'll probably not want to charge in as well!
The other horse tactic would be to ride up to the line, then throw something: axe, javelin, shoot an arrow etc... into the mass of men and then ride off, then wheel round and do it again and again. The psychological impact of charging your beast towards the line would also be a big thing to help frighten the enemy. These big animals can be very intimidating. The Sassanian Persians used this technique a lot, to soften up the enemy, with their formidable elite cavalry.
As a prime example of this problem, I present the orcs at the battle for minas tirith in the lotr films. They made a spearwall, but hesitation and fear meant it was a mess before the rohirrim even arrived. No doubt a contributing factor to the way the cavalry rode over them so easily.
The only problem with this is that they were all holding 'baby' spears! No wonder they were s******* themselves
. Plus they didn't really have much in the way of good shields that could interlock.
A Greek hoplite would have had a spear of up to 9 feet long, the re-emergence of heavy infantry in the medievel period to counter the armoured knights i.e. the Scottish
schiltron or the later
Landsknecht armies would have had significantly longer pikes - I believe anything up to 22 feet or so. A body of men with a forest of pikes that long would require a mad animal to charge into - I think!
The Uruk-hai were better adapted in the movie against Rohan, as they seemed to have loads of pikemen, but they still lost - well I suppose LotR is not meant to be realistic
.