A young guy, shy, loner, usually a teenager, often a geek, who does not have a girlfriend and spends far too much time alone, often at home in his bedroom, when other people his age are out having a good time. The implication being that he is wanking
, i.e. "Pumping" his mattress because he can't root
a real Sheila
. The meaning of the term and it's use might have nothing in common.
An underage drinker (under 18) is in a bar and bumps, into a grown man who shouts at him, "Piss off, you little mattress pumper".