I have written 4 query optimizers: HypersonicSQL, H2 database, PointBase Micro, and Apache Jackrabbit Oak. I know procedural language don't have such optimizers. But I argue that you don't always need them. I argue that the database engine query optimizer is often more a risk than a help. I have seen missing indexes far too often. SQL doesn't require that the programmer thinks how the data is accessed, so the result is that too many programmers don't think about it, and so don't add indexes, request too much data, and so on. The SQL statements work fine with small (development) data sets, so the same statements are used with production databases, and you run into problems too late.
I don't think it's hard to write procedural queries that are always fast. You write a loop or map/filter/collect method. You just explicitly need to mention which index to use, is all.
Agree on the missing indexes, was in a debugging session yesterday where missing indexes turned out to cause some issues.
But I have little faith that people who forget indexes are capable of writing procedural queries that are always fast. These less experienced devs are the ones that benefit most from the query optimizer doing the hard work. I think the real solution is for database to automatically create indexes where needed, as some database already do. And some better visual query editor like ultorg.
I don't think it's hard to write procedural queries that are always fast. You write a loop or map/filter/collect method. You just explicitly need to mention which index to use, is all.