MongoDB: Index intersection does not eliminate the need for creating compound indexes

This is a quote from the MongoDB docs:

https://docs.mongodb.com/v3.0/core/index-intersection/#index-intersection-and-compound-indexes

But it doesn't explain why it doesn't eliminate the need for compound indexes. Why doesn't it?

There are a couple of reasons for this at present (I say at present, because improvements may be introduced in the future that reduce the relevance of these points):

• Index intersection is limited to 2 indexes (this was the case in 2.6 but I don't see anything in subsequent release notes to suggest this has been increased)

• If you can predict your query patterns and cover them with compound indexes then this will likely perform better

• Index intersection is currently essentially a last resort to provide some index based coverage (as opposed to a table scan for example) for query patterns that are not already covered by a more efficient method

This is true for most purposes and most query profiles. A counter example here would be a set of queries, regularly run, which would require a large number of compound indexes. The work being done to keep those indexes up to date, the amount of memory they take up might mean that index intersection is a better strategy. This, however, is an edge case - most database usage can be profiled and indexed efficiently so that index intersection is not required.

StackExchange Database Administrators Q#158240, answer score: 4