This has not been my experience of using technology effectively. Without an understanding of the implementation details, you inevitably use something inefficiently or for not quite the right purpose.

I cannot imagine anyone using a database effectively on any significant amount of data without understanding indexes, how different joins work, why join order is important, what effect join orders have on performance, etc. Get to a certain scale and it's not enough to know about indexes; you need to understand the structure of b-trees, disk I/O performance, how CPU cache performance affects b-tree navigation even when index is cached in memory, how to use compound indexes effectively to reduce random access through the index, etc.

The constraints of CPU and memory never go away, and if you're trying to scale something, you're going to be limited on either or both of those resources. That in turn forces you to understand execution and memory behaviour of the abstractions you're working with. All abstractions leak when pushed.