There's been times where I've had to spend quite a bit of time trying to change the SQL in subtle ways to get an index to be used and do the join algorithm type I wanted; when it didn't want to do so. Usually improved performance by a massive factor for the datasets I was working with (e.g. 10x). The predictable performance as well was a big factor - I would prefer predictable and adequate performance over peak performance but high variability every time.

In the end after all this frustration I wished I could have written the query plan directly, especially when I used Postgres with no query hints. And yes I'm aware of Postgres and all the tricks that you can do to make it do certain types of joins and such and I employed many of them (adding statistics, loose index scans, all the index types and others). IMO the potential this could open up is quite large given many databases all have the indexes/algorithms and many data structure types these days. Gluing them together in a performant way where you use the appropriate algorithm/data structure index for the data on tables/JSON blobs/etc seems to be the hard part right now that requires a lot of trial and error and learnings of the SQL optimizer to get right.