From the fantasies experienced when smoking an opium pipe, equivalent to pipe + dream. First attested in the 1800s; compare Old Englishpīpdrēam(“the sound or music of a pipe”), which is an identical yet separate and unconnected formation.
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.