From the fantasies experienced when smoking an opium pipe. First attested in the 1800s; surely not a direct reflex of Old Englishpīpdrēam(“the sound or music of a pipe”).
I think that his plan to become a professional athlete is a pipe dream and that he should stay in school.
1962 December, “Dr. Beeching previews the plan for British Railways”, in Modern Railways, page 376:
Only a year ago it would have needed a "super-Micawber" to be optimistic that the railways would once again pay their way. But it was no longer a pipe dream that B.R. could make a profit, the way to do it was now clear.