Aggregation using COLLSCAN when it should use IXSCAN

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?

1 Like