If deciding join order and what indexes to use sounds like your idea of a good time. It certainly isn't mine.
x = binary_search(column_name=id, value=56, show_all_columns=True)
y = dictionary_search(column_name=id, value=56, show_all_columns=True)
z = join(x, y, joinFunc=func(a,b)(a==b))
The example above could be a join. With the search function name itself specifying the index. If no such index is placed over the table it can throw an exception: "No Dictionary Index found on Table"
This is better api design. However, years of optimization and development on SQL implementations makes it so that most no-sql api's will have a hard time catching up to the performance of SQL. It's like googles V8. V8 is a highly optimized implementation of a terrible language (javascript) which is still faster than wasm.
However, I think specifying the algorithms in the queries is really not a good idea. Your performance characteristics can change over time (or you might now know them at all yet when you start the project). With your solution, if you, e.g. realize later that it makes sense to add a new index, you'd have to rewrite every single query to use that index. With SQL, you simply add the index and are done.
Declarative yes, but unlike SQL my example is imperative. An imperative language is easier to optimize then a functional or even a expression based language (SQL) because computers are basically machines that execute imperative assembly instructions. This means the abstractions are less costly and have a better mapping to the underlying machine code.
>However, I think specifying the algorithms in the queries is really not a good idea. Your performance characteristics can change over time (or you might now know them at all yet when you start the project). With your solution, if you, e.g. realize later that it makes sense to add a new index, you'd have to rewrite every single query to use that index. With SQL, you simply add the index and are done.
Because the DB sits over a bottleneck in web development you need to have explicit control over this area of technology. If I need to do minute optimizations then am api should provide full explicit control over every detail. You should have the power to specify algorithms and the language itself should never hide from you what it's doing unless you tell choose to abstract that decision away...
What I mean by "choose to abstract that decision away" is that the language should also offer along with "binary_search" a generic "search" function as well, which can automatically choose the algorithm and index to use... That's right, by changing the nature of the API you can still preserve the high level abstractions while giving the user explicit access to lower level optimizations.
Or you can memorize a bunch of SQL hacks and gotchas and use EXPLAIN to decompile your query. I know of no other language that forces you to decompile an expression on a regular basis just to optimize it. Also unlike any other language I have seen literally postgres provides a language keyword EXPLAIN that allows users to execute this decompilation process as if they already knew SQL has this flaw. If that doesn't stand out like a red flag to you I don't know what will.
SQL on the otherhand... EXPLAIN is used on a regular basis, it's built in to the programming language and rather then just mark lines of code with execution time deltas it literally functions as a decompiler to deconstruct the query into another imperative language. This is the problem with SQL.