That "magic carpet" ride does come at a price but it is not monetary: the softer the tyre, the more resistance and with that the slower the ride.
Unless you're riding on a velodrome, pumping your tyres up to 120+psi doesn't decrease rolling resistance, infact the the opposite is true. If the tyre can deform around small bumps it rolls much more easily. This is why very expensive and fast tyres have casings with a high thread count (sometimes even made of silk) so they are more supple.
Something else to take into account is the aerodynamic trade off as tyre width grows. I think I read somewhere that 25mm is roughly the sweet spot. What you gain in lower rolling resistance by going up to 28mm you loose in drag. That's a bit anecdotal (sorry) but I have heard it mentioned in a couple of places. The best source I could come up with was from Zipp's web site.[2]
Of course that might well be worth it to be more comfortable if it means you can keep your body in a more aero position as that will dwarf any loss from the tyres (again a guess).
Hopefully there's some numbers somewhere to back that up. Maybe Bicycle Quarterly or Tour Magazine might be a good bet for anyone interested.
Also https://www.bicyclerollingresistance.com/ is always very interesting ...assuming you obsess over bike tyres as I'm sure everyone does. :P
[1] https://www.youtube.com/watch?v=Clk_LLBYFzA [2] https://zipp.com/support/faq/faq.php
My 23c handmade FMB tubulars (definitely not cheap) are a much nicer ride than cheaper tyres.
Rolling resistance is far more complicated, on anything other than a wooden velodrome, the road surface actually means that there is a sweat-spot in terms of pressure - too low and rolling resistance will be high, as you say, this decreases up to a point at which the effects of the microbumps in the road start to cause energy losses through hysteresis and eventually the tyre bouncing over tiny bumps.
For a 23c tyre and a 70kg rider the optimum tyre pressure is actually typically in the 80-90psi range (5.5-6ATM) although again this is dependant on tyre quality, an expensive tubular tyre with very supple silk sidewalls can be run at higher pressure than a touring tyre with tough reinforced sidewalls.
In other words, I use my bike as I use my motorbike: as a form of transport. Maybe that takes a different approach, maybe not, as said I don't know how you use your bike. What I do know is that I generally don't hold with the 'cultures' which form around specific areas, no matter whether it is biking (silk-walled handmade tyres adapted to your personal preference), audio (audiophile-grade left-turning 99.999999% oxygen-free oriented-strand meteoric copper power cables), food (organic free-range lettuce grown on virgin soil from heritage seeds fertilised with certified manure from Swiss highland cattle) and other such things.
And tire construction. Though price likely does not correlate well with how supple the tire construction is.