>The real problem with abstractions is when they are implemented poorly, or have side-effects, or just plain bugs.
Which is always, and is why clean maintainable code uses minimal abstractions to accomplish the task. However it seems the default these days has become "pile it on".
There is also the issue that lower abstractions don't always give enough info to higher level extractions, and vice-versa. Take OS filesystem code for example. It would be useful for the low level storage driver to have info that the higher level FS possesses so it can tell the hardware that certain blocks of storage can go back into the write leveling pool (i.e., when a file gets deleted). Or, the other way around, the filesystem driver can make better file allocation and access patterns if it knows that the minimum write size for a block on an SSD is 128KB, or that starting a write on one block, and ending on another, gives better performance if that first block is picked to be on a stripe boundary on a RAID drive.
The problem isn't minimal abstraction but imperfect abstraction. Imperfect abstractions convince people to dig underneath the surface. Now you've lost the benefit of abstraction because they're thinking of two layers at once.
So when people run explain on their SQL query, IMO you've broken the declarative abstraction.
Asking the SQL engine to explain the query doesn't mean the abstraction is broken or imperfect. It simply means the developer wants to understand how their query is being understood and, hopefully, optimized. This is essential when debugging why queries don't behave optimally, e.g. failure to use an index.
Even with the perfect abstraction, people will need to dig beneath the surface. This is because every layer of code is subject to side-effects due to its actual implementation. That's why we have, e.g., multiple sorting algorithms, and documentation for their individual behavior, speed, etc.: so developers can understand the limits and idiosyncrasies of each and pick the correct one for the job.
We may make incremental steps towards more bug-free code, but because everything is limited by the details of its actual implementation (whether real or an abstraction), there is no magic bullet that will make a huge leap forward in the realm of software development as a whole.
The abstraction for SQL is related to how the work gets done, not what it produces. The missing piece is for the user to be able to control that optimization of how the work gets done, which is getting right down into the details of what SQL was abstracting in the first place.
There could be a middle ground where the user provides input about target performance and the engine uses that input during choice of execution plans.
Maybe an OPTIMIZATION clause for difficult or long running queries:
ALLOW_EXTENDED_PLAN_SEARCH=True
or
MINIMIZE_EXECUTION_TIME_FACTOR=10 (1=just do simple, 10=expend more time+space up front to reduce overall time)
User does not want to control optimizations. User wants their queries to run fast enough. Ideally database should monitor the queries and create/remove necessary indices as needed. And some databases do that.
It's true, it would nice if the user didn't need to intervene, but unfortunately query optimization is a combinatorial problem where the engine has incomplete information causing performance problems (or cost problems if you are on Snowflake), so the user is required to intervene some percentage of the time.
There are no perfect abstractions. Or they are extremely rare. There are abstractions suitable for a given task. And as requirements change, perfect abstractions become imperfect and new abstractions are needed (which would be imperfect in the past).
Which is always, and is why clean maintainable code uses minimal abstractions to accomplish the task. However it seems the default these days has become "pile it on".