I'm currently experimenting with upgrading mongo, comparing performance of different mongodb versions and stumbled into a curious anomality when upgrading from 3.4 to 3.6.

I have a relatively large collection with an index that looks like this:

{ a: 1, 'array.b': 1, 'array.0.date': -1 }

And the following query:

    "array.b" : ObjectId(...),
    "a" : ObjectId(...)
}, { ... bunch of fields }).sort({ "array.0.date" : -1 }).skip(0).limit(26);

In 3.4 I see that Mongo reliably uses the proper index and explain shows that it uses IXSCAN as a first step with exactly 26 works, as I would expect.

In 3.6, for some reason (I didn't find explanation) explain returns 2 rows. The 1st one looks exactly like the one used by 3.4, and the 2nd row shows that 1st IXSCAN returns all documents matching first two fields in the index resulting in a regular SORT being performed.

It wouldn't bother me if without explain mongo would end up using 1st row which would be an optimal way, but in reality ends up using an in-memory sort.

Is it possible to make 3.6 use specific execution plan (returned as the 1st row of explain)?


Update 1: found a relevant note in blue here (https://www.mongodb.com/docs/v3.6/tutorial/sort-results-with-indexes/), going to test if moving array fields to the top level would help, array.b is also an array, so who knows...

1 Answer 1


As the Update 1 link mentions, sorting was changed in 3.6 (and later seemingly reverted in 4.4). Moving the date field (array.0.date) to the top level was enough to make this sort of queries behave properly in 3.6+, and I didn't have to do anything with array.b.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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