Any way to force mongo use index sort? Works in 3.4, and breaks in later versions

Working with Data
,
1

Hi there!

I have an index that looks like this:

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

Then there is a query for which this index was specifically tailored:

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

Mongo 3.4 picks the right index and uses it to get exact 26 records to work with, resulting that explain shows 26 works in total on step 1.

Later versions (so far tested 3.6 and 4.0) for some reason never use the index to sort records and query ends up traversing every document matched by first two index fields and then sorting everything in memory. Even when Mongo considers using the mentioned index (either by hint or examining execution stats for a specific plan) it still does the same.

I’m trying to figure out a possible upgrade path without performance degrading heavily due to such index misuse. Any tips?

Thanks!

2

Hey @Vasiliy_Naumov, the reason this no longer works is due to a change in MongoDB 3.6 regarding array sort behavior.

1 Like
3

Hi Alex, and thank you for the response! I’m sorry I didn’t reply later. I found this by accident shortly after posting the result, but didn’t verify the cause until a couple days after your post.

FWIW, if anyone is interested in possible remedies for this issue - we ended up duplicating array.0.date to top level and changing index to { a: 1, ‘array.b’: 1, date: -1 }, and it worked. So, it turned out that at least we didn’t have to move array.b up to make index sort work properly, which is nice.

Once again thanks, I appreciate your response very much!