Missing Index when Aggregate Restored by snapshot.

[alkampfergit]

Consider an Aggregate with 70.000 commits with a snapshot on 69.900 commits. The query issued to mongo to retrieve the last 100 commits is this one (grabbed from mongo profiling)

db.Commits.find({
"BucketId" : "xxx",
"StreamId" : "Container_16015",
"StreamRevisionTo" : {
"$gte" : 69900
},
"StreamRevisionFrom" : {
"$lte" : 2147483647
}
})
.sort({StreamRevisionFrom : 1})
.explain()

Database has this index

{
"BucketId" : 1,
"StreamId" : 1,
"StreamRevisionFrom" : 1,
"StreamRevisionTo" : 1
}

That can partially satisfy the previous query, because of the order of StreamRevisionFrom and StreamRevisionTo (inverted respect the index). This causes a full index scan for that aggregate.

When the system is under load, this can lead to poor performance. Possible solutions are

1. Change the query to issue StreamRevisionFrom and StreamRevisionTo into the correct order and verify that the index is now used effectively
2. Add another index (less preferable, because it wasted space and insertion time)

{
"BucketId" : 1,
"StreamId" : 1,
"StreamRevisionTo" : 1,
"StreamRevisionFrom" : 1
}

[alkampfergit]

Created a patch in commit 08bd584e99e86e1af394e5511e835802b7a6cd3b in branch hotfix/5.3.4 but did not created unit test to confirm the increase of performance.