Aggregation using COLLSCAN when it should use IXSCAN

Working with Data
,
1

I’ve been reviewing some alerts in Atlas related to a high examined:returned ratio, I can see in the logs instances where a given aggregation query was run as a COLLSCAN but when I copy that query out to Robo3T it is run as an IXSCAN. We are only logging queries that exceed 100ms so I can’t tell whether the query is always running as a COLLSCAN or just occasionally.

Are there circumstances that would cause this behaviour, such as when a read is directed to the Primary instead of the Secondary? Or can the indexes become locked up when there is high traffic?

3 Likes
2

We are seeing the same behaviour since we upgraded to version 7.0

Did you find any explanation for this behaviour?

1 Like
3

If you’ve found a solution, we’d greatly appreciate it if you could share it with us.

In the meantime, to ensure MongoDB consistently uses the expected index, I recommend adding a hint() to your query. This will explicitly direct MongoDB to use the specified index, preventing the possibility of a COLLSCAN and helping maintain optimal query performance.