Here's how I understand that battle very briefly: Caesar was outnumbered and being pushed-back/defeated. Pompey sends in his cavalry to crush Caesar's men and start a route. But Pompey's cavalry runs into an unexpected line of Caesar's infantry and - brilliantly - Caesar has ordered his men in that line to use their pila against the horses (as soldiers would traditionally use spears). The horses are spooked, and Caesar's men win.
I understand that the pila wasn't a traditional spear. But even so, my question is: Why was it so unexpected/successful to use a spear-like weapon against cavalry? Wasn't using spears against cavalry and horse-mounted-troops a known tactic?
Using spear-like weapons against cavalry was indeed a very old trick even the Romans knew. The problem wasn't that the tactic was a surprise.
The issue Pompey had was that he did not trust his (relatively) green troops against Caesar's (extremely) veteran troops.
Pompey had also given his infantry an unusual order, telling them not to advance to meet the enemy, but to remain stationary and throw their pila as soon as the enemy came within range. Both of these decisions suggest that Pompey doubted the effectiveness of his own legionaries compared with Caesar’s more experienced soldiers. Instead of relying on them, he planned to win the battle with his cavalry. Concentrated on the left flank, they outnumbered Caesar’s horsemen by around seven to one. They would advance to open the battle, smashing their opponents and then wheeling round to take Caesar’s infantry from the flank and rear. Labienus was in charge of this attack, and may even have devised the plan. The enemy’s deployment made the massive superiority of their cavalry obvious.
So Caesar notices he is outnumbered, both in terms of infantry as well as cavalry, and despite this Pompey wouldn't commit to a straight assault.
Caesar also knew that Pompey's 7000 strong cavalry was led by Labienus. One of his own turncoat commanders and had served with Caesar for the better part of a decade.
To counter this, Caesar took a single cohort from the third line of each of his legions and stationed this force behind his own cavalry, probably echeloned back from the infantry line. This fourth line was concealed behind the horsemen and Legio X and not observed by the enemy. Both armies were now ready for battle, though some delay may well have elapsed as the commanders encouraged their men.
The genius in this isn't the straight forward application of disciplined pike > cavalry. It's the fact that Caesar knew his opponent incredibly well and set up the perfect defense to counter Labienus.
Most of the Pompeians remained in position, but their cavalry surged forward against Caesar’s horse, which gave way. During the charge and subsequent combat the Pompeians seem to have fallen into some disorder, the individual squadrons losing formation and merging into one great mass. Many of these horsemen were relatively recent recruits and neither officers nor men had much experience of operating in such large numbers, but there was always a tendency for this to happen if cavalry became too crowded. Suddenly, Caesar gave the signal for his fourth line to attack. The legionaries charged forward, yelling their battle cry and sounding their trumpets, and then using the pila as spears. The result was almost immediate panic, which spread throughout the mass of enemy cavalry until there was a great stampede to the rear. The supporting light infantry were abandoned and massacred or dispersed by the legionaries. Pompey’s main attack had failed.
At this point the cavalry smashing through Caesar's troops is clearly not happening, Pompey doesn't dare advance and jeopardize his troop's cohesion, and Caesar's infantry attacks. Brilliantly.
In the meantime the main infantry lines had come into contact. Caesar’s men had begun their charge at the usual distance, turning their steady forward march into a run preparatory to throwing their pila, but had then noticed that the enemy was not moving. To prevent the cohorts from running too far, losing their formation and maybe wasting their missiles, the centurions halted the line.
The nonchalance with which Caesar’s men paused and redressed their ranks such a short distance from the Pompeians was another indication of their superb discipline. Reformed, the attack went in, the legionaries waiting until they were within 50 feet or so of the enemy before throwing a volley of missiles and charging sword in hand into contact. The Pompeians replied with their own pila—though it is doubtful if the men in the rear ranks of each cohort can have thrown their weapons effectively. A hard struggle developed, the second line of each army, which always acted as close supports to the first line, soon being drawn in. However, the fourth line followed up its success by attacking the exposed left flank of the Pompeian infantry throwing that section of the line into disorder.
This is just after Pompey (almost immediately after Labienus' cavalry attack failed) ran and left the field. All Caesar needed to do was send in his last reserves and watch as most Roman troops surrendered. The 'allies'/'subjects' of Pompey were run off or butchered.
Source; Caesar’s Civil War 49–44 BC by Adrian Goldsworthy