I have a Rails application that is using mongoid as a mongo wrapper. Given a batch of 1,000 mongo bson ids, I run a simple query:
Case.where(:_id.in => case_ids).to_a
Here, case_ids, is the array of 1,000 bson ids.
This fires a Slow query warning:
{"t":{"$date":"2021-10-31T10:25:23.555-04:00"},"s":"I", "c":"COMMAND", "id":51803, "ctx":"conn16","msg":"Slow query","attr":{"type":"command","ns":"app_development.audita
bles","command":{"getMore":1879838943203028675,"collection":"cases","$db":"app_development","lsid":{"id":{"$uuid":"cc709ec6-788d-4908-a8d8-5c55c678dea9"}}},"originatingC
ommand":{"find":"cases","$db":"app_development","filter":{"_id":{"$in":[{"$oid":"60e49287e55e88201c22637d"},{"$oid":"60e49287e55e88201c22637f"},{"$oid":"60e49287e55e8820
1c226381"},{"$oid":"60e49287e55e88201c226383"},{"$oid":"60e49287e55e88201c226385"},{"$oid":"60e49287e55e88201c226387"},{"$oid":"60e49287e55e88201c226389"}
This is using the mongo _id and I've confirmed that it has an index (as it should). There are only 85,000 records in the collection. Any idea why this is firing so many getMores and why it's hitting so many records? When the query is done it returns:
{"$oid":"60e49287e55e88201c2265ad"},{"$oid":"60e49287e55e88201c2265af"},{"$oid":"60e49287e55e88201c2265b1"},{"$oid":"60e49287e55e88201c2265b3"},{"$oid":"60e49287e55e88201c2265b5"}]}}},"planSummary":"IXSCAN { _id: 1 }","cursorid":1879838943203028675,"keysExamined":38102,"docsExamined":36514,"cursorExhausted":true,"numYields":38,"nreturned":36515,"reslen":13165126,"locks":{"ReplicationStateTransition":{"acquireCount":{"w":39}},"Global":{"acquireCount":{"r":39}},"Database":{"acquireCount":{"r":39}},"Collection":{"acquireCount":{"r":39}},"Mutex":{"acquireCount":{"r":1}}},"storage":{},"protocol":"op_msg","durationMillis":101},"truncated":{"originatingCommand":{"filter":{"_id":{"$in":{"282":{"type":"objectId","size":12}}}}}},"size":{"originatingCommand":1600124}}
The most striking of which is:
"keysExamined":38102,"docsExamined":36514,"cursorExhausted":true,"numYields":38,"nreturned":36515,
And
"locks":{"ReplicationStateTransition":{"acquireCount":{"w":39}},"Global":{"acquireCount":{"r":39}}
Is there something wrong with my indexes or query, and is there a better index I can run to support faster .in queries, or is there some precompute I can do so that when I need to pull out a subset of docs, it's faster?
Thanks for any help, Kevin