> I've read countless articles on how to rearrange the "declaration of what you want" in order to get the database to do it in a fast way.

While this is doubtlessly true, in many cases the “rearranging” also involves a subtle change in what you are asking the database to do, in ways which allow the database to actually do less work.

SELECT 1 WHERE EXISTS vs WHERE ID IN (SELECT ID FROM mytable WHERE …) is a great example. The former is a much simpler request despite functionally doing the same thing in the common use-cases.

Yes, although both MySQL and Postgres will automatically rewrite the latter to the former, in most circumstances. I still find it good practice to explicitly write the semijoin, to demonstrate the intent.

MySQL, but not Postgres, will also convert the inverse to an antijoin.

As an aside, this is also often a great example to demonstrate why you shouldn’t use SELECT *, since you can’t write a semijoin if you need the predicate column in the result.

I don't see how that is supporting "SQL is declarative and therefore simple". You've kept the declarative part, but it's not simple anymore. You need to know multiple declarative ways to ask for the same thing, and the consequences of each. Might as well just tell the DB how you want to do it.

Sometimes you do, yes. Often times, though, the issue is that the statistics for the table are wrong, or the vacuum (for Postgres) hasn’t been able to finish. Both of these are administrative problems which can be dealt with by reading docs and applying the knowledge.

I think of RDBMS like C: they’re massively capable and performant, but only if you know what you’re doing. They’re also very eager to catch everything on fire if you don’t.