How MongoDB choose optimal path for $OR operator?

As Technical Services Engineers, we often encounter a common question: Why isn’t MongoDB selecting the correct index?

In this blog post, I’ll delve into how MongoDB determines the most efficient plan for a query that includes the $OR operator.

A general rule of thumb to create an efficient compound index is to arrange the index keys by applying ESR rule. However, when dealing with a contained $or(where $or is inside of a nested query), MongoDB’s planner aims to distribute common clauses by pushing them into the $or branches. This broadens the selection of available plans.

Let me allow to illustrate this using the below scenario. Suppose you’re managing a  “products” collection.

The collection “products” has the following indexes presently.

We need to find items for a specific category, say  “Electronics“, priced either below $200 or available in stock.

db.products.find({"category": "Electronics","$or": [{"price": { "$lt": 200 } }, {"quantity": { "$gt": 0 }}]})

The above query retrieves all items belonging to the “Electronics” category, priced below $200, or having available stock. In logic terms, it’s expressed as:

find = category="Electronics" AND (price<200 OR quantity>0)

Abstracting the field names, the query simplifies to:

find = a AND (b OR c)

which can be reformulated as:

find = (a AND b) OR (a AND c)

While { category: 1, price: 1, quantity: 1 } could serve as a good index; during the query planning phase, MongoDB considers  { category: 1, price: 1 }and { category: 1, quantity: 1 } to be better indexes in this particular scenario. As a result, the OR query may not utilize the compound index and instead opt for the other indexes.

Based on the explain plan provided above, the query planner identifies “category_1_quantity_1” as the optimal index.

I hope this explanation sheds light on the behavior of the MongoDB OR operator.