To me, this is explained by the idea that we will be competing, not cooperating, with the ultimate winner.

If I am observing the contest and not participating, I would want the weaker party to win so that the remaining population dominates me less. If David gets lucky and beats Goliath, I only have to compete with David in future contests - if Goliath wins, I may have to go up against him next time.

Intuitively, this seems to explain the tendency quite well - a political victory by, let's say, Canada over the US, feels like it would "take them down a peg" and reduce the power imbalance between the UK (where I am) and the US. Equally a defeat of Roger Federer by a low-ranked player reduces my feelings of inferiority compared to the "superhuman" Federer.

Of course this would be quite different if I were entering a doubles contest - I'd much rather be Federer's partner - or choosing sides in the US-Canadian war. I don't think the underdog effect survives if I'm actually involved in the fight.