node.jsmongodbmongodb-queryaggregation-frameworkmongodb-indexes

will mongodb use index to sort when $or used in $match?


compound index

{
  A: 1,
  timestamp: 1,
}

and 

{
  B: 1,
  timestamp: 1,
}
db.user.aggregate([
  { $match: {
      $or: [ { A: "some_value" }, { B: "some_value" } ]
  } },
  {
    $sort: {
      timestamp: 1
    }
  },
  {
    $limit: 1000
  }
])

The question is, will the $sort and $limit stage be a blocking stage because of the $or in the $match.

Without the $or, I know that the index will be used to perform sort.


Solution

  • I think a slightly better way to phrase the question is:

    Can the indexes provide the requested sort thus preventing a blocking stage when the database processes this aggregation?

    The reason that I say this and emphasis "Can" is because there are generally no guarantees about plan selection by database optimizers. So it is often beneficial to understand what it is capable of doing and, to some degree, the benefits of the approach, in order to appropriately reason about what might happen.

    Disagreeing with the response in the comment, the answer to the rephrased question is that the database can indeed use the indexes to provide the sort. Putting your example into this mongoplayground example yields an explain plan that includes a SORT_MERGE stage. This is a streaming variant that combines the sorted sequences from scanning the two indexes in the requested order.

    This is furthermore confirmed directly in the documentation here which states:

    $or and Sort Operations When executing $or queries with a sort(), MongoDB can use indexes that support the $or clauses.

    Again, just because the system can do this does not mean that it always will. There are a number of factors, notably the number of clauses in the $or operator, that would influence the likelihood of such a plan being selected.