Index Selection and the Query Optimizer

While we all know that the Query Optimizer uses Indexes to produce better execution plans, we don't all know exactly which indexes will give the...