A large part of cavalry's effectiveness relied on its shock value. A hundred guys with lances charging towards you with all the speed they can is absolutely TERRIFYING since the reptilian part of your brain sees "big angry animal coming at me fast".

Now, its worth noting that there were classically several types of cavalry. Heavy cavalry were essentially what I described above, essentially the tanks of their day. Light cavalry would serve as scouts and harassment forces used to (in differing eras):

Chase down fleeing enemies

Harass enemy archer/javelineer/auxiliary formations

Seek and destroy the enemy support train, including artillery and supply wagons

Scout ahead of the main force and assist in foraging

Raids

Heavy cavalry, meanwhile, would generally be used for direct engagement on the front. Generally speaking, they would either attempt to

flank the enemy line, then begin rolling it up from one edge (where infantry might take over)

Break the enemy line via shock and momentum, allowing your infantry to envelop part of their line/cause incredible morale damage/etc.

Counter enemy cavalry assaults

In later years, cavalry archers and dragoons got more and more emphasis. This roughly coincided with the end of heavy cavalry as a major thing since the cost of a heavy cavalryman was too great for the number of casualties they would suffer. Note that this ignores the mongols and Huns, both of whom placed enormous emphasis on cavalry archers.

What region or time period are you interested in?