We know from performance testing that index intersection plans are faster than single-solution plans when
- The size of the intersection is small.
- The amount of data that would need to be fetched by the single-index solutions is large, so that
- this data would exceed the available memory, or is generally not in the working set.
However, our testing also shows that index intersection plans may not be chosen by the ranker in these cases. We should consider tweaking plan ranking such that the ranker is more likely to choose intersection plans.