The problem with SQL is that SQL that works today will be too slow tomorrow. Fixing it involves trying to please the query planner, all while making your query less and less comprehensible.
As a contrasting example, the experience you get with ElasticSearch is that queries have predictable performance even as your data changes. In that way it's much, much nicer. On the other hand, you lose joins, which is a huge downside.
You miss the point. ElasticSearch just do some gardening for your data automaticaly, where in pure SQL you need to do it yourself, but there are tools for it.
As a contrasting example, the experience you get with ElasticSearch is that queries have predictable performance even as your data changes. In that way it's much, much nicer. On the other hand, you lose joins, which is a huge downside.