> as soon as you do something actually complicated, you realize that the different optimization modes of the engine give different results
The same is true of SQL query planners. You can perform basic queries without understanding how your SQL engine of choice works under the hood, but if you want performance, you must understand how your DB works. SQL is just the interface.
This is different in kind from imperative programming languages (which are much closer in abstraction to the underlying machine architecture), but we rub along with SQL ok; why not Prolog?
Yeah, but the difference is that SQL provides a huge number of ways to solve the "My query got slow when it got complicated" problem. In Prolog, you have the cut operator, and when that stops working for your usecase, you're just SOL.