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.