[SERVER-15658] Query with $in and $nin doesn't use index correctly Created: 14/Oct/14  Updated: 14/Oct/14  Resolved: 14/Oct/14

Status: Closed
Project: Core Server
Component/s: Performance, Querying
Affects Version/s: 2.6.4
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Petr Bela Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-12281 When choosing multikey index bounds, ... Backlog
Operating System: ALL
Participants:

 Description   

When matching an attribute against both $in and $nin, Mongo doesn't use the index correctly.

If only $in is used, then index takes advantage of that:

db.assets.find({
  tags: {
    $in: ['blah']
  }
}).explain()
 
{
  "cursor" : "BtreeCursor tags_1",
  "isMultiKey" : true,
  "n" : 6,
  "nscannedObjects" : 6,
  "nscanned" : 6,
  "nscannedObjectsAllPlans" : 6,
  "nscannedAllPlans" : 6,
  "scanAndOrder" : false,
  "indexOnly" : false,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "millis" : 0,
  "indexBounds" : {
    "tags" : [
      [ "blah", "blah" ]
    ]
  }
}

However, if $nin is involved, instead of finding documents that match $in and then filtering out those that don't pass the $nin condition, it scans all documents.

db.assets.find({
  tags: {
    $in: ['blah'],
    $nin: ['cat']
  }
}).explain()
 
{
  "cursor" : "BtreeCursor tags_1",
  "isMultiKey" : true,
  "n" : 75760,
  "nscannedObjects" : 79974,
  "nscanned" : 1197016,
  "nscannedObjectsAllPlans" : 79974,
  "nscannedAllPlans" : 1197130,
  "scanAndOrder" : false,
  "indexOnly" : false,
  "nYields" : 9351,
  "nChunkSkips" : 0,
  "millis" : 2331,
  "indexBounds" : {
    "tags" : [
      [ {"$minElement" : 1}, "cat" ],
      [ "cat", {"$maxElement" : 1} ]
    ]
  }
}

Related SO issue: http://stackoverflow.com/questions/26351714/query-with-in-and-nin-doesnt-use-index



 Comments   
Comment by Petr Bela [ 14/Oct/14 ]

Cool, I thought it'd be already reported but couldn't find it. Thanks.

Comment by Ramon Fernandez Marina [ 14/Oct/14 ]

petrbela, this was previously reported in SERVER-15214, which is actually a duplicate of SERVER-12281. Please tune in for SERVER-12281 for updates and feel free to vote for it.

Regards,
Ramón.

Generated at Thu Feb 08 03:38:37 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.