Index with $or and $sort

I have a query like this db.getCollection(‘test’).aggregate([{$match:{or:[{"CreatorId":7},{"Users.._id":7}]}},{$sort:{“CreateTime”:-1}},{$limit:20}])

As the documentation says, all of the $or clauses must be in the index, so I created three indexes: CreatorId, Users._id and CreateTime.
However when I run the explain, it shows only CreateTime index was used, therefor the query time is very long.
What is the correct way to make an index for this query?
Thanks!

In case I was not clear enough, I want to have an index that can hit on all “CreatorId”, “Users.$._id” and “CreateTime” fields

Hi @yuda_zhang,

The documentation on $or Clauses and Indexes indicates that when evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans.

The pipeline you shared however is not simply filtering, but also sorting. The optimizer in the case of your example is selecting a plan that will Use and to Sort the Query Results as this will prevent an in-memory (blocking) sort.

The result is a full index scan of { CreateTime: -1 } then all documents are fetched and filtered for the conditions in the $match stage of the pipeline. This is the default behavior prior to MongoDB 4.4 (due to SERVER-7568) as the Aggregation Framework favoured non-blocking sorts.

To address this each branch of the $or should also account for the sort criteria as follows:

db.test.createIndex({ CreatorId: 1, CreateTime: -1 })
db.test.createIndex({ "Users._id": 1, CreateTime: -1 })

Note that the order of fields matters when creating a compound index. For more information on this see the blog post at https://www.alexbevi.com/blog/2020/05/16/optimizing-mongodb-compound-indexes-the-equality-sort-range-esr-rule/.

3 Likes

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.