"Infantry have powder and bayonet, thus the cavalry must have the thunder of cannons for their sabres"

A very rough paraphrase from Napoleon.

Cavalry wasn't just effective; they were at their most effective. The Napoleonic era saw the last mass usage of inaccurate muskets, which by firepower alone could not stop a concentrated corps of Cavalrymen. Thus we have squares; which offered prime targets to artillery; which was proliferated in hitherto unseen amounts. Cavalry, when concentrated (a novelty at this time) and skillfully supported by massed fire, could decimate an unsupported infantry line; and frequently did. Cavalry had to be committed when supporting fire was at its greatest, and enemy fire at its lightest; and we see many charges be carefully concealed by dead ground and conducted only after extensive counter battery fire.

Kellerman at Marengo, supported by infantry and artillery fire, annihilated an Austrian column attempting to re stabilize the line; the charge concentrated several hundred horsemen, had a storm of shot and shell to support it and was perfectly timed to minimize incoming fire. This example alone should serve; though there are countless others of varying size one can find with a quick search that will further confirm this.

Source: Chandler, David; "Campaigns of Napoleon"

Cavalry were effective in three major parts of the battle and campaign, the beginning of the campaign and battle, the battle itself, and the follow up. All three of which are primarily seen in the Jena Campaign.

First, I must discuss the different types of cavalry used by the French. Light cavalry was made up of Hussars, Chasseurs a Cheval, and Chevau-legere Lanciers (or light horse lancers/lancers). Next there was the medium cavalry, the Dragoon that served as both light and heavy cavalry but trained as mounted infantry and cavalry, while flexible it wasn't particularly great at anything. Finally you have the heavy cavalry, the Cuirassiers, Carabineers, and famously the Grenadiers a Cheval which were used more as a battering ram rather than anything concerning movement.

Prelude

The Light cavalry is the eyes and ears of an army. During a campaign, the light cavalry would fan out into a screen and look for the enemy. For Napoleon and the Jena Campaign, this meant moving forward and into Prussia to locate both information and the enemy. Famously General Antonie-Charles de Lasalle rode into towns and immediately have his men and horses provisioned while he gathered whatever maps the town had in order to use them. Further, light cavalry would act as a harassment tool, interrupting lines of communication and baggage trains in order to disrupt enemy ability to fight.

Once the enemy is located, that's when Napoleon would start to pull forces together. The light cavalry would help to guard the corps as they came together and sometimes act as bluffing forces, moving into areas to make it appear that the French were coming from a direction.

The Battle

During the battle, light cavalry would be holding the enemy in and harassing units when possible. The heavy cavalry would hold until when ordered. When ordered forward, such as at Jena, the heavy cavalry would canter, trot, then charge home into an enemy weak point. The benefit of this would be the shock value. Light cavalry were on small, fast horses that would sustain long riding, but heavy cavalry would be on large heavy horses with large men, so when the charge happened, it would be almost like a battering ram on a line.

The point of a heavy cavalry attack is that it is on a weak area, the men are tired and have low morale, they're almost broken and then seeing large horses with large men on them rushing forward, it's a clincher. Yes they have a musket with a bayonet but that doesn't count when you're freaking out and seeing a wall of heavy horses barreling toward you.

Denouement

With the enemy scattering, the cavalry pursues. Jena is the textbook follow up to a battle, where the cavalry push out and harder to defeat and already defeated enemy. After Jena, the Prussians put up very little resistance because their army was completely destroyed at Jena-Auerstedt.

There are of course other instances of cavalry. Lancers were technically able to poke at the enemy that was in square formation but that wasn't very smart because the enemy could still fire at the lancers. In Spain, Polish lancers were able to defeat a British battalion by using a squall as cover and was able to destroy it before it could respond in a square.

Conclusion

Cavalry was about exploitation rather than simple combat. The use of cavalry makes a difference and a skilled commander would hold cavalry back until it was absolutely needed. Napoleon held back his cavalry at Jena until he decided that there was a place to exploit in the Prussian line. Once the heavy cavalry was unleashed, it was a slaughter and the panicking men ran as the light cavalry joined in.

Exploitation, it's all about exploiting the enemy's weaknesses.

They were equipped with bayonets, but not pikes. A line of infantry isn't going to stop a charge of heavy horse with just bayonets. They had to deploy into a square (as mentioned above made them vulnerable to artillery and concentrated musket fire) to be able to defend against a charge. By presenting a smaller front to be charged, completely covered on all sides by rows of bayonets, firing at the precise moment(as close as ten paces) to deliver the most lethal dose of firepower at the incoming horsemen, did they fully protect themselves.

Cavalry were almost always repulsed by square formations. Hedges of bayonets that protected layers of firing muskets was virtually impenetrable to cavalry, but very vulnerable to return fire from muskets and especially artillery.

However, when not in square formation, the advantage was firmly in the hands of the cavalry, especially when attacking from the flank or rear. At Albuera, Colborne's British brigade was virtually exterminated when caught deployed in a line formation. Columns stood a better chance, but still they were very susceptible. There just wasn't enough physical resistance or density of fire to stop a cavalry charge in a short period unless you could redirect the impact as you could in a square formation.

Source - The Spanish Ulcer - David Gates