How the optimizer may use an index for cardinality estimates without actually using it

Recently I was sent a data warehouse SQL query that performed badly. It took more than an hour and timed out. It was the first time that query was run with a particular parameter. Here I will show that to solve this, I created an index to give the optimizer better information, even though it did not actually use it to execute the query. Continue reading