Actually the CTE uses the table indexes quite nicely, just review the query plan and you will see it pulls the indexes from the table. (Most of the time) 😀

I do agree however sometimes it is hard to figure out where in a large CTE the performance is slow. Usually the best bet, also the longest time waster is to break apart the query and run each building on each until you find the slow query. I just did that same process for a weighted search and found I was missing a few indexes on some key look up columns that should have been found earlier when I was using the tuning wizard.