It's really not but you'd be surprised how many people don't know the basics of how indexes work. I've had people tell me that their queries couldn't be optimized further because "they had already added an index for every column of the table".
It doesn't help that SQL, by design, hides the actual algorithm doing the data access from the users while simultaneously relying on them to add indexes to achieve performance, which is in my humble opinion the worst mistake of SQL.
I want them to say “the heck are you talking about, because that’s not how anything works?”… it does trip some people up though that just legitimately don’t understand what’s happening.
(I only skimmed -- far too much "Joan explains that" to signal ratio.)
Interesting read on a project implementing this for Postgres: https://pganalyze.com/blog/automatic-indexing-system-postgre...
It will tell you _what_ it's doing, but not _why_ it's doing it. Unraveling the mysteries of the query planner is a dark art that is still firmly in the realm of graybeards.
Adding indexes can increase I/O time (more writes), increase DB file sizes (more stuff to store) etc, which are not inconsequential. Sometimes it's also as simple as moving indexes to a tablespace stored on an SSD.
It's really not very hard, basically add indexes wherever lookups are done but don't go overboard, especially with tables that see a lot of inserts. The DB will tell you what it's doing if you ask, it's all easily investigated. If your joins resist optimisation your data structure is probably off.
> I won't even touch on proper design, which requires someone with significant database expertise from the get-go
In more extreme cases this is true, but a few basic principles and an understanding of databases will get you 95% of the way there and is quite intuitive. Something as basic as "don't copy data around, each individual entity exists in one place, and use its key to refer to it otherwise" will help enormously.