I experienced similar behavior with an older version of Spring Data and this was my take on it: https://arnoldgalovics.com/how-much-projections-can-help/
I had a talk with Oliver Gierke (Spring Data lead) and he made some improvements (that’s why you get so “good” results 🙂 ) but basically there will be always a cost on having abstractions vs coding it manually.
This is a trade-off as everything else is. On one hand you got flexibility, easier development, less maintenance (hopefully), on the other hand you get full control, a bit uglier query model.