$nin operator avoid query execution over a specific index

XMLWordPrintableJSON

    • ALL
    • Hide

      create some documents

      for (i = 0; i < 100000; i++) {
db.test26.insert(
    {"_id" : new ObjectId, 
        "user" : "mario", "removed" : false, "objId" : i, "created" : new Date(),
        "p" : { "id" : ["content"+i,"content"+(i+1),"content"+(i+2)]   }, 
        "a" : "attributea"+i,"b" : "attributeb"+i,"c" : "attributec"+i}   );
    }

      create some indexes

      db.test26.ensureIndex({ "user" : 1,"removed":1,"a" : 1 });
db.test26.ensureIndex({ "user" : 1,"removed":1,"b" : 1 });
db.test26.ensureIndex({ "user" : 1,"removed":1,"c" : 1 });
db.test26.ensureIndex({ "user" : 1,"removed":1,"p.id" : 1 });
      Show
      create some documents for (i = 0; i < 100000; i++) { db.test26.insert( { "_id" : new ObjectId, "user" : "mario" , "removed" : false , "objId" : i, "created" : new Date(), "p" : { "id" : [ "content" +i, "content" +(i+1), "content" +(i+2)] }, "a" : "attributea" +i, "b" : "attributeb" +i, "c" : "attributec" +i} ); } create some indexes db.test26.ensureIndex({ "user" : 1, "removed" :1, "a" : 1 }); db.test26.ensureIndex({ "user" : 1, "removed" :1, "b" : 1 }); db.test26.ensureIndex({ "user" : 1, "removed" :1, "c" : 1 }); db.test26.ensureIndex({ "user" : 1, "removed" :1, "p.id" : 1 });
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      summary

      A query with $in and $nin operator does not work over the special index. This query works correctly in mongodb version 2.4.3

      db.test26.find({"user":"mario","removed":false,"p.id":{"$nin":["content5"],"$in":["content1","content2","content3"]}
 }  
).explain()

      the query should run over the index user_1_removed_1_p.id_1, but it works only if is not present the $nin operator.
      This is the output of explain function

      {
    "cursor" : "BtreeCursor user_1_removed_1_a_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 100000,
    "nscanned" : 100000,
    "nscannedObjectsAllPlans" : 194167,
    "nscannedAllPlans" : 219855,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 2234,
    "nChunkSkips" : 0,
    "millis" : 789,
    "indexBounds" : {
        "user" : [ 
            [ 
                "mario", 
                "mario"
            ]
        ],
        "removed" : [ 
            [ 
                false, 
                false
            ]
        ],
        "a" : [ 
            [ 
                {
                    "$minElement" : 1
                }, 
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : "PZNVWINDEV-011:27017",
    "filterSet" : false,
    "stats" : {
        "type" : "KEEP_MUTATIONS",
        "works" : 101084,
        "yields" : 2234,
        "unyields" : 2234,
        "invalidates" : 0,
        "advanced" : 0,
        "needTime" : 100000,
        "needFetch" : 1083,
        "isEOF" : 1,
        "children" : [ 
            {
                "type" : "FETCH",
                "works" : 101084,
                "yields" : 2234,
                "unyields" : 2234,
                "invalidates" : 0,
                "advanced" : 0,
                "needTime" : 100000,
                "needFetch" : 1083,
                "isEOF" : 1,
                "alreadyHasObj" : 0,
                "forcedFetches" : 0,
                "matchTested" : 0,
                "children" : [ 
                    {
                        "type" : "IXSCAN",
                        "works" : 100000,
                        "yields" : 2234,
                        "unyields" : 2234,
                        "invalidates" : 0,
                        "advanced" : 100000,
                        "needTime" : 0,
                        "needFetch" : 0,
                        "isEOF" : 1,
                        "keyPattern" : "{ user: 1.0, removed: 1.0, a: 1.0 }",
                        "boundsVerbose" : "field #0['user']: [\"mario\", \"mario\"], field #1['removed']: [false, false], field #2['a']: [MinKey, MaxKey]",
                        "isMultiKey" : 0,
                        "yieldMovedCursor" : 0,
                        "dupsTested" : 0,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0,
                        "matchTested" : 0,
                        "keysExamined" : 100000,
                        "children" : []
                    }
                ]
            }
        ]
    }
}

            Assignee:
            Unassigned
            Reporter:
            flavio alberti
            Votes:
            6 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: