you can get pathological queries that you think should work just fine, but that due to some optimizer decision actually go off the deep end...

Agreed. I think with DB's in particular a sound understanding of how they do their business is very useful. Both in DB design and in writing efficient queries. In fact the number of times that I've heard people say "I dont need to know how DB's work, its a black box" was a motivation for writing this node.

Also this type of understanding has on at least one occasion earned me signifigant brownie points. The Sybase DBA I work with the most, (who is now my boss) once wondered aloud why when dropping a clustered index and rebuilding it didnt always result in the same record ordering. The reason of course was that internally Sybase was using an unstable sort and two records who were key equivelent might be reordered. The fact I was able to explain this to the man that has taught me practically all of the Sybase stuff I know earned me a lot of respect. So sometimes the payoff isnt even in the code domain, but in real life.