I have a sharded collection with around ~ 200M documents, and an index on this collection: {datasetId: 1, _id: 1}, where datasetId is the shard key on the collection.

When I query on the collection with the following query:

find({datasetId: 'YYY', _id: ${gt: ObjectId('XXX')}}.sort({_id: 1})

The query does not use the aforementioned index, it uses the {_id: 1} index, and takes a very long time to run, while greatly increasing the mongo servers cpu usage.

Here are the query execution stats for a datasetId where ~ 40K documents satisfy the query, and an _id which is the oldest available that has specifed datasetId - which took 28.6 seconds:

If I hint the index on the same query it uses it and runs very quickly scanning only the needed documents:

Using any other operator than $gt /$gte works ($lt / $lte on the newest available ObjectId, $ne - all use the index with no need for a hint), the greater than operators are the only ones that do not use the index (example of a $lt query):

I cannot hint at the index each in query of this nature, Why do only the $gt and $gte operators not use the index? seems they should act like any other operator and use the index if it is available?