Here and here you asked if those indexes were missing. Just adding these in is essentially night and day in terms of profile load speeds. These indexes should definitely be considered.
It is true that the cache added here: Slow Page Loads on User Profiles - #12 by codinghorror has made a huge difference but that’s only on subsequent loads, the indexes improve the initial experience over all. Users should not be subjected to slow load times even if they get better due to the cache for the time that is cached. The goal was to improve the first initial load (when ever the cache expires) and this accomplishes that big time.