If that's not what you need, there are plenty of procedural languages whith which you describe how to get things.
A query plan will change based on statistics. It's the engine's job to decide if your query is best served by parallelizing it to multiple cores, or deciding if it's worth JITing it before execution.
The actual execution of the query is an implementation detail of the engine, and should be. That's the entire point of a SQL standard: To provide users with an interface to talk to engines, which then retrieve the data you asked for.
It is its core strength and the reason why it's so successful.
The entire archive of the pgsql users' mailing list disagrees. Every wants to know why their plan is suboptimal, or why it changed. People also want to know why the planner takes 100ms to generate the plan and only 1ms to execute the query, and so forth.
The idea that you just say what you want and you get the optimal result from your database is just ridiculous to anyone who has had to use them under any significant load.
This seems like mere selection bias.
As the sibling comment points out, users who have no problem aren't likely to post "Everything is fine!" to the mailing list. In fact, it would likely be rude to do so.
However, database engines aren't perfect -- I know I've encountered bugs in older SQL server versions where the query never finishes but making some trivial adjustments fixes it. This is a bug. And most mailing lists are filled with people encountering bugs. Saying what you want and getting the best result is exactly what you should expect.
And rather a lot of the archives of $scripting_language_of_your_choice are people confused about duck-typing/type system failures. That doesn't mean scripting languages should be replaced with statically typed ones; just that there are pain points in every system, and right (or wrong) tools for every job.
Don't believe me? Check how much of the FAQ traffic from first-time Rustaceans (or Swift/Java/etc. newcomers) has to do with how to satisfy their language's type system.
You pick your poison. SQL gives you a clearly defined set of tradeoffs up front. If that's not for you, no worries, move along.
That's like arguing you should be able to dictate the assembly that your compiler produces. The entire point of SQL (and most compilers) is that they can use their knowledge to optimize the result in ways that are too difficult or too involved for humans to do. Most people cannot out-optimize a compiler in the general case. And most people cannot out-optimize a SQL DBMS.
Also, with SQL, the result might be highly dependent on the data itself. A table with 1,000 rows yesterday might be queried entirely differently from the same table now with 100,000 rows. Are you going to constantly go back and dictate the query plan to the engine every few months as the data changes? Probably not. Use the tool as intended and you'll be fine. Anything else is premature optimization at best.
A straightforward Postgres database will almost certainly fulfill your performance requirements unlsss you have a pretty edge-case scenario. Under those circumstances, it would be foolish to start by assuming that you are doing something that the database cannot easily accomplish. After all, it’s much easier to migrate the parts of your stack that require custom code to a new infrastructure when you reach scale, rather that trying to constantly patch bugs and performance issues in your shitty wannabe SWL engine!
Millions of users beg to differ.
