Here are a few sample questions which understanding the database can help with:
- Why is my query slow?
- Why do I need indexes?
- Why isn't the optimizer using my indexes?
- Why is it important to use joins rather than putting all merge logic in code?
- Why is the database falling over?
I've seen every one of these. I've sped up queries by breaking one flexible one into multiple similar ones (each one fed a different condition which causes a different query plan to be best). By having many similar one be replaced by one parametrized one (to reduce parsing). By telling it to use a particular index. By getting it NOT to use a particular index. By pushing logic down to the database. By pulling logic out of the database into code (because I knew how it should do it and the database optimizer got it wrong).
There is no simple rule for when to do each thing. But every case I could explain why I did what I did if you understood algorithms. (And if you didn't understand algorithms, my explanation would have made no sense.)