Drupal - Should I be using node_load() rather than building my own queries with db_select()?
Technically, yes, writing your own db_query() statements will return the data faster and with less overhead. However, it is not really the "Drupal way" to do things.
node_load() provides a 'static' cache; in other words, if you load the same node twice in one page request, it will only hit the DB once.
What you are really missing out on by not using node_load() is all the API hooks and third-party module integration. If you need a CCK field, node_load() gets all that data for you, where as a manual query would require a lot of joins.
As you note, there is a lot of extra processing/loading going on that you may not need at the given moment. If you don't need the data, it won't technically hurt you not to have it. It is really up to you if you want to go this route.
The biggest reason IMO for using node_load() even though you might not need the extra data is just for the purpose of standardization. You won't have to worry about what pieces of data you have at a certain point. You will have a fully loaded node and will have more options for adjusting the data as necessary through hook_nodeapi(). Other developers looking at your code will have an easier time understanding what they are working with. Also, 6 months down the road when you go to add a new feature/display field you wont have to adjust your queries.
node_load is definitely a resource hog, but i have found it better to use in my experience. Drupal 7 greatly improves the situation by providing node_load_multiple() which, as its name implies, reduces the number of queries when loading multiple nodes.
As explained in jakarska's answer, node_load(), node_load_mutliple() do more than just DB querying. They take care of calling all the node building hooks implemented by the enabled modules of your site.
In addition, in Drupal 7, with the introduction of pluggable field storages, when working on re-usable code, you can't known whether or not your field data will be available in your (main) database. As a general rule, I would use node_load(), node_load_mutliple() and EntityFieldQuery to query for nodes. If you only need a specific field for multiple node you can also use field_attach_load() with an $options['field_id'] on the results of an EntityFieldQuery.
So, I wouldn't go for a custom SQL query unless the code is sure to be used in cases where everything that node_load(), node_load_mutliple() and EntityFieldQuery provide are not, and will never be, needed and on data that are, and will always, be stored in the SQL database.