3

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?

3

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.

  • docs.mongodb.com/v3.6/core/index-intersection now states "MongoDB can use the intersection of multiple indexes to fulfill queries. [1] In general, each index intersection involves two indexes; however, MongoDB can employ multiple/nested index intersections to resolve a query" - so maybe it can use more than 2 indexes now? – Nico Sep 12 '19 at 20:31
  • I'll be honest, I have not kept up with releases since around 3.2 or so, and there have been so many advances in MongoDB in that time that the pros/cons for pre-defined compound indexes versus relying on intersection have drastically changed. I am afraid it will take someone more up to date than I to speak to it though :/ – Adam C Sep 13 '19 at 0:13
  • I'm looking very closely at some plans that would benefit from index intersection (they're distinct() queries) and it's often not even considering intersection. So there may have been some improvements, but I'm not exactly floored by the (current) results. – Dylan Brams Jan 1 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.