SQL indexing is definitely something I need to learn more about.

At the same time, it feels a bit like it's become such a standardised fallback approach that people will sometimes throw indexes at a problem instead of applying some thought to the query.

Admittedly, I'm basing this on a few personal anecdotes. For example, a long time ago an acquaintance posted a question on Facebook about speeding up a query in a database of astronomy data, where they were filtering based on a triangle-inequality style formula a^2 + b^2 < c^2. The first five comments said they would just index the a, b and c columns, which would probably work but the author didn't have the right permissions on the DB and only needed to run the query once anyway. I didn't know much SQL at the time but suggested trying a quick initial filtering of the form a + b > c on the data before calculating the formula with squares. This apparently sped it up sufficiently for their purpose.

Over time I’ve found this site good for getting the basics across.

As an SRE with a database background this should be required reading for any development team.