For the background on this, please see http://drupal.org/node/1067802.
Given all that, what situations exists where I might want to use db_select(), or should I be relying solely on db_query?
There are 5 reasons to use SelectQuery
You are building dynamic queries with a varying number of conditions, joins, fields and so on. See field_read_fields() for an example.
You want to use so called Extenders. Example extenders are PagerDefault (replaces pager_query()) and TableSort (replaces tablesort_sql()). These allow to add additionally functionality to SelectQuery. See also How do you make sortable tables with a pager with data from a custom table?. An example: node_page_default().
You want to allow other modules to alter your queries. Then you can add so called tags and SelectQuery will automatically call a corresponding alter hook for that tag. I'm am heavily relying on this with my Privatemsg module (We already did that in D6 with a custom query builder).
If you want/need to use the node_access system to only show nodes the user is allowed to see. Just add the 'node_access' tag to your $query. This replaces db_rewrite_sql().
SelectQuery has a few features that help to make your code work the same across all supported databases. For example there is SelectQuery::orderRandom(). And if you have a LIKE condition, ->condition('field', $value, 'LIKE') will make sure that it is always a case insensitive comparison. In D6, you had to use LOWER() for that which was much slower. But AFAIK, there aren't more than this two right now.
If none of these reasons apply for a specific case, use db_query().
The documentation about
Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead.
I always use db_select as I prefer readability, maintainability and cross database compatibility over small performance gains. Moreover, I think the numbers given in the mentioned issue give a wrong image of the overall performance. We are talking a 300 microseconds difference on a query that, when returning more than a single column often run in the multi millisecond range. And I wouldn't be surprised if there is some 1-time only overhead (class loading) and thus that the differences for a full (page) request are far less.