The heavy cavalry charge dominated warfare for centuries for a reason.
Re-enactment studies have shown that, even under those circumstances where the 'infantry' knows they're not going to be harmed/killed, they are undergoing off the charts anxiety/terror. Heart rates spike dangerously high, hyperventilation, the ground literally shakes under your feet, the sound is louder than thunder, hands gets slick with sweat, limbs lose strength/coordination, bladders/bowels open, vision and hearing become affected, etc. And even in the modern age, with test subjects accustomed to Mack trucks and trains and the like, they universally talk about the fear of the sheer size/mass of the horses coming towards them. In an age when they'd have been the biggest thing going, and they really were coming to kill you, it must have seeme suicidal, like a modern man standing in front of an oncoming train, gambling that the conductor will stop it in time. Your entire nature is screaming at you to run away...your fight/flight switc has been flipped and you can't even try and balance it by aggression, you're just going to stand there and hope what you were (often briefly) taught comes true...because every brain cell and evolutionary instinct is telling you that this is sheer madness.
And it very often was. Movies and Cornwell et al have revised perceptions to where it's simply a pragmatic calculation, horses won't charge spears, so just stand your ground, but...
1) Everyone knew that during the centuries the HC charge dominated, too. It wasn't some Renaissance notion. But it was still the decisive arm for so long because knowing something as an academic truth, and gambling your life on it in the moment are very, very different things. Soldiers in the phalanx age also all knew that most casualties happened after one side breaks...so just don't break, right? But no, they still broke.
2) Heavy cavalry horses were specifically trained for their agressiveness and courage. So, in spite of nature, they still often DID charge headlong into spears...and even if you impale a charging heavy horse, the odds are it will still push deeply into you/your ranks, causing all kinds of destruction. So you can win and still lose, as an individual.
3) Remember, too, that it's not just your fear you need to overcome to withstand a charge. You need to be sure that the guys around you will do likewise. Otherwise you're the sitting duck standing there doing the right thing on his own. This is an incredibly important factor. Any sense that your neighbours are breaking/about to break almost inevitably leads to your fight/flight instinct, already kicking into overdrive due to imminent big metal death charging towards you, pretty much taking control of the ship and you'll just run away on autopilot.
Cavalry charges were still very effective well into the modern age. Remember the disastrous charge of the Light Brigade? Pure folly, an absolute mass suicide, perfect death trap...and yet many still got through and penetrated the Russian lines....people forget that. Russian soldiers afterwards even talked about being terrified in the face of the oncoming charge, in spite of the fact that the majority didn't have a chance of even reaching them.
Because the cavalry charge's de-romanticization in the past few decades has in fact created an alternate fiction wherein it's just a matter of long sticks and guts. But it's not. Yes, if you are going to win you need those, but those don't mean you're going to win, and the fact that you can eventually train men to stand in there doesn't mean they'll succeed. In the Napoleonic age men could be trained to calmly march in slow step, pause, kneel, load, fire, stand, reload, fire etc. in the face of enemy fire and artillery...doesn't mean it wasn't often suicidal to do so. And we know that experience doesn't render men immune to the terror of facing a charge...veterans who had survived many cavalry charges were still wont to break in the moment. Until gunpowder, it was far and away the most terrifying event a soldier would ever see in his life, and all the training and experience in the world couldn't make that untrue in that moment where you're standing on shaking ground with sweaty hands, a heart bursting in your chest, eyes dimming and blurring with sweat, the growing sound of approaching thunder as huge iron clad monsters are coming right for you, right at you, and all you've got is a stick and the hope your mates with sticks won't do what your instincts are screaming for you to do too; get the hell out of the way of the oncoming deathtrain.