A Swedish-Prussian allied army under the overall command of Carolus X did indeed run into a force of Tatars allied with the Polish in the late 17th century. Alas, the Tatars didn't last too long in a straight fight against the cuirass-wearing, pistol-toting, hyper-disciplined regular cavalry neverming their infantry support (who not too long afterwards tore the valiant but futile charge of the Polish Hussars to pieces with murderous volumes of fire) and were quickly chased off the field.

Such "irregular" light cavalry forces had considerable military value in Early Modern and later warfare, but usually not in pitched field battles. They made far better scouts, harassers and ravagers than line troops, the exception perhaps being Napoleonic-era Russian Imperial Guard Cossacks who could take on and rout armored cavalry with their long lances. (Though Napolonic heavy cavalry had just a helmet and a breastplate...)

Now, one thing all sources I've seen agree on is that when cavalry attacks infantry in close combat the single most important thing for the infantry is to stand fast or even counter-charge should they be (over)confident enough to do so (most weren't, and for a good reason). This has more to do with the nature of horses than anything else - the beasts are very careful about their footing and will pretty much flat out refuse to run full tilt into what to them appears like a solid, immobile obstacle in their path. I've been told horses can "brake" awfully fast in a pinch, and by most accounts it seems that if the infantry held ranks and didn't begin to scatter the most of the horses would pull short before impact and the horsemen would have to "duke it out" the old-fashioned way.

Of course, having a long spear or lance means the cavalryman can hit the infantry when the horse/rider pair still has momentum left and can better attack them without risking himself or his mount - for example the Swiss halberdiers with their three-meter shafts turned out to have a major problem with the four-meter chivalric lances in an open-field battle and as a result were integrated into the pike squares.

If the infantry loses its nerve and begins to waver, nevermind run away, then the cavalry can charge home for full effect. Once this happens the infantry are in serious trouble, as then the horsemen can ride over and amongst them with relative impunity and make full use of their weight, speed and height advantage. Around Napoleonic times it was observed that even relatively small cavalry forces could utterly obliterate even large ifantry concentrations in a matter of minutes should they catch them unformed, and the same seems to have been the case since humans learned to use horses in war (chariots obey the same basic principles).

If the infantry stands and fights (if only because the pressure of the back ranks keeps them from going anywhere), however, it becomes less important what they're armed with. Pikes, spears, polearms and anything else which grants great reach and/or killing power are obviously advantageous, but for example Roman legionaires could and did beat Persian cataphracts with just their short swords so it's not really required.

Be that as it may, where cavalry was used it usually made up the specialist attack arm of any army well into the 19th century and the appereance of
rapid-fire rifles and machineguns. There were exceptions naturally, usually bought about by geographical constraints - many parts of the world are quite simply ill suited for raising horses or have large amounts of terrain where cavalry loses much of its power (Scandinavia has both, which gave the Swedes a bit of a problem to overcome when they started building their Great Power status in the 17th century).

Outside the battlefield, on campaign, the role of the cavalry concentrates on its mobility. Mounted men are simply far better at longe-range reconnaissance, foraging and devastation than the poor footsloggers can ever be, and naturally the best way to keep enemy cavalry from tearing up your hapless foragers is to screen them with your own cavalry. Ditto for skirmishers, though foot archers backed up by spearmen or similar anti-cavalry troops also work (the "Franks" mostly used this technique down in the Middle East). The superior mobility of mounted men also enables them to carry out surprise attacks or secure strategic bottlenecks away from the main force, which is naturally highly useful.

On the downside horses are a pain on logistics. This is particularly the case with the grain-fed, stable-bred warhorses "civilized" nations used, as fodder had to be brought along or scrounged up and the beasts could not subsist for long on just grass, but also afflicted the steppe nomads and their vast herds of grass-munching ponies. Grasslands to graze on are anything but a certainity outside the steppe, and even there they're a bit season-dependent and quickly exhausted by a passing army.

It's probably not exactly a coincidence the Mongols never proceeded too far outside the Great Eurasian Steppe, and certainly the nomadic conquerors who did (Hungarian-Magyars, Avars, Toba, Huns, you name it) soon had to give up the pastoral life and start feeding their horses from the peasants' larders...