A charge does not look like what Hollywood shows us, at all. In fact, most charges never even make contact with the enemy. Whether on foot or on horseback, you have to be suicidal to physically smash into a well formed enemy line at speed, and horses are quite fearful animals as well.
So, if the enemy does not show any sign of budging, you simply....stop your charge (supposedly does not even require a conscious decision by the officer in charge, but often happened on its own), retreat to a safe distance if missile weapons are involved, then wait for another opportunity or until your side or the other loses patience and gives a charge another go.
If the enemy does yield ground, but in an orderly fashion, you still don't press the charge (still suicidal), but do keep 'possession' of the ground yielded by the enemy (major factor in moving battle lines).
If the enemy and you are equally stubborn bastards, fed up with waiting, fine, you do make contact, but ,by the gods,
not at a gallop or sprint like in Hollywood (again, suicidal), and both sides start poking each other with pointy things. Since pretty much everyone involved is more interested in avoiding to get poked by the other sides' pointy things than in succeeding to poke an enemy, casualties usually remain pretty light (on a "per round" basis) until everyone gets tired(IIRC, based on f.e. boxing and LARP, about 15-20minutes, tops), and one or both sides retreats a bit (outside of the biggest danger zone for whatever missile weapons are around). The non-retreating side, if any, is usually too tired to go in much of a pursuit, and needs a drink just as much as you do, so you can repeat this back and forth a couple times, just like the previous 2 stages (that can all be mixed up in any order, going both ways).
This can go on for hours and hours with
fairly equal (having further reaching pointy things than the other side, or armour that can resist heavier blows, is
very helpful in making this part less equal*) single-digit percentage total casualties on both sides, until, finally, one side is suddenly heavily demoralised by seeing a leader fall, or maybe just plain exhausted (or is a Macedonian phalanx and finally ends up sufficiently disrupted from accumulated small losses and terrain features, despite having forced the enemy back the entire battle up to this point), and then on the next charge they receive they will break, run in sheer terror while losing formation, and be hunted down and slaughtered by the new victors who just got an extra adrenaline rush.
You can, of course, reach the final stage in the first 15 minutes of a battle, if, say, you have an unexperienced, ill equipped peasant militia being charged by a famous heavy cavalry force, known for its numerous victories.
OTOH, if you have experienced, veteran Swiss pikemen in their underwear, without armour, but with their pikes, against heavy lancers in full plate armour (and for some strange reason zero missile troops around), the lancers, whose pointy sticks have a shorter reach than those of the Swiss, will fail as long as the nerves of the Swiss hold out.
Or, both sides have highly motivated, high quality troops and stubbornly refuse to break until both armies are well into double digit percentage casualties, and that's where you get your "costly victories", "hard fought victories" and "Pyrhic victories".
This is a long winded way of saying that "a case where cavalry do manage to annihilation the first 3 ranks in the charge, that's about as well as they are going to do", is actually not very likely to happen, not in
one charge at least.
This also ignores things like flanking, surprise attacks in the rear, unusual fanaticism, the effect of the terrain etc...
Oh, and "Does this mean that they were employed only to hope the enemy would rout?" Well,
only is certainly an exaggeration, but in a way...yes, totally. Because being conceptually aware of the fact that a cavalry charge pretty much never succeeds against infantry holding firm (preferably with longer spears than the cavalry) is a very different thing from actually having the balls to stay put. A charging mass of heavy cavalry must be an absolutely
terrifying sight if you have never seen it before, or worse, did when the result was the annihilation of the regiment next to yours.
Part of this from the excellent
A Collection of Unmitigated Pedantry blog (sorry, can't find the most relevant blog posts, there's several involving Romans, Spartans, Gondorians or Dothraki, amongst other examples, the guy is rather...productive)
For the morale & repeatedly charge back-and-forth on the battlefield parts, also "collective impressions" so to speak, from various pages on
Napoleonistyka, as the Napoleonic Wars are one of the first of which truly large numbers of letters and memoirs are still available, not only from generals, but also from common soldiers.
* Most famously, the Greek hoplites in the Persian Wars both had that longer reach and more resistant armour than the Persians, with the known results.