Loading...

Type: Bug
Resolution: Duplicate
Priority: Trivial - P5
Fix Version/s: None
Affects Version/s: 2.4.2
Component/s: Index Maintenance, Querying
Labels:
None

Operating System:
Linux

Steps To Reproduce:

Hide

1) create a testindex collection
2) insert some documents

db.testindex.insert([
{   
    "user" : "mario",
    "removed" : false,
    "objId" : "299939",
    "p" : {
        "id" : "content1"
    },
    "a" : "attributea",
    "b" : "attributeb",
    "c" : "attributec"
},
{
    "user" : "mario",
    "removed" : false,
    "objId" : "299938",
    "p" : {
        "id" : "content2"
    },
    "a" : "attributea",
    "b" : "attributeb",
    "c" : "attributec"
},
{
    "user" : "mario",
    "removed" : false,
    "objId" : "1129229",
    "p" : {
        "id" : "content3"
    },
    "a" : "attributea",
    "b" : "attributeb",
    "c" : "attributec"
},
{
    "user" : "mario",
    "removed" : false,
    "objId" : "6600954",
    "p" : {
        "id" : "content4"
    },
    "a" : "attributea",
    "b" : "attributeb",
    "c" : "attributec"
},
{
    "user" : "mario",
    "removed" : false,
    "objId" : "8889998",
    "p" : {
        "id" : "content5"
    },
    "a" : "attributea",
    "b" : "attributeb",
    "c" : "attributec"
}]
)

3) create some indexes

//generic ingex on collection
db.testindex.ensureIndex({ "user" : 1,"removed":1,"_id" : 1 }); 
// index over the objId
db.testindex.ensureIndex({ "user" : 1,"removed":1,"objId" : 1 });
//index over p.id
db.testindex.ensureIndex({ "user" : 1,"removed":1,"p.id" : 1 });

4) optimized query

db.testindex.find({"user":"mario","removed":false, "$or" : [ { "objId" : "299939"} , { "p.id" : "543534"}]}) // the query use the two indexes user_1_removed_1_objId_1 and user_1_removed_1_p.id_1

// explain result
{
    "clauses" : [ 
        {
            "cursor" : "BtreeCursor user_1_removed_1_objId_1",
            "isMultiKey" : false,
            "n" : 1,
            "nscannedObjects" : 1,
            "nscanned" : 1,
            "nscannedObjectsAllPlans" : 1,
            "nscannedAllPlans" : 1,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nYields" : 0,
            "nChunkSkips" : 0,
            "millis" : 0,
            "indexBounds" : {
                "user" : [ 
                    [ 
                        "mario", 
                        "mario"
                    ]
                ],
                "removed" : [ 
                    [ 
                        false, 
                        false
                    ]
                ],
                "objId" : [ 
                    [ 
                        "299939", 
                        "299939"
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor user_1_removed_1_p.id_1",
            "isMultiKey" : false,
            "n" : 0,
            "nscannedObjects" : 0,
            "nscanned" : 0,
            "nscannedObjectsAllPlans" : 0,
            "nscannedAllPlans" : 0,
            "scanAndOrder" : false,
            "indexOnly" : false,
            "nYields" : 0,
            "nChunkSkips" : 0,
            "millis" : 0,
            "indexBounds" : {
                "user" : [ 
                    [ 
                        "mario", 
                        "mario"
                    ]
                ],
                "removed" : [ 
                    [ 
                        false, 
                        false
                    ]
                ],
                "p.id" : [ 
                    [ 
                        "543534", 
                        "543534"
                    ]
                ]
            }
        }
    ],
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "millis" : 0,
   
}

5) Incapsulate the or inside an other $or or $and

db.testindex.find({
    "user":"mario","removed":false,     
    "$and" : [ {
    "$or" : [ { "objId" : "299939"} , { "p.id" : "543534"}]}
    ]}
    ).explain()
	
	
{
    "cursor" : "BtreeCursor user_1_removed_1__id_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 5,
    "nscanned" : 5,
    "nscannedObjectsAllPlans" : 5,
    "nscannedAllPlans" : 5,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "user" : [ 
            [ 
                "mario", 
                "mario"
            ]
        ],
        "removed" : [ 
            [ 
                false, 
                false
            ]
        ],
        "_id" : [ 
            [ 
                {
                    "$minElement" : 1
                }, 
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
   
}

Mongodb is not able to rewrite the nested $or into the canonical rooted $or form, thus can't make use of the index union.

Show

1) create a testindex collection 2) insert some documents db.testindex.insert([ { "user" : "mario" , "removed" : false , "objId" : "299939" , "p" : { "id" : "content1" }, "a" : "attributea" , "b" : "attributeb" , "c" : "attributec" }, { "user" : "mario" , "removed" : false , "objId" : "299938" , "p" : { "id" : "content2" }, "a" : "attributea" , "b" : "attributeb" , "c" : "attributec" }, { "user" : "mario" , "removed" : false , "objId" : "1129229" , "p" : { "id" : "content3" }, "a" : "attributea" , "b" : "attributeb" , "c" : "attributec" }, { "user" : "mario" , "removed" : false , "objId" : "6600954" , "p" : { "id" : "content4" }, "a" : "attributea" , "b" : "attributeb" , "c" : "attributec" }, { "user" : "mario" , "removed" : false , "objId" : "8889998" , "p" : { "id" : "content5" }, "a" : "attributea" , "b" : "attributeb" , "c" : "attributec" }] ) 3) create some indexes // generic ingex on collection db.testindex.ensureIndex({ "user" : 1, "removed" :1, "_id" : 1 }); // index over the objId db.testindex.ensureIndex({ "user" : 1, "removed" :1, "objId" : 1 }); //index over p.id db.testindex.ensureIndex({ "user" : 1, "removed" :1, "p.id" : 1 }); 4) optimized query db.testindex.find({ "user" : "mario" , "removed" : false , "$or" : [ { "objId" : "299939" } , { "p.id" : "543534" }]}) // the query use the two indexes user_1_removed_1_objId_1 and user_1_removed_1_p.id_1 // explain result { "clauses" : [ { "cursor" : "BtreeCursor user_1_removed_1_objId_1" , "isMultiKey" : false , "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "nscannedObjectsAllPlans" : 1, "nscannedAllPlans" : 1, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "user" : [ [ "mario" , "mario" ] ], "removed" : [ [ false , false ] ], "objId" : [ [ "299939" , "299939" ] ] } }, { "cursor" : "BtreeCursor user_1_removed_1_p.id_1" , "isMultiKey" : false , "n" : 0, "nscannedObjects" : 0, "nscanned" : 0, "nscannedObjectsAllPlans" : 0, "nscannedAllPlans" : 0, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "user" : [ [ "mario" , "mario" ] ], "removed" : [ [ false , false ] ], "p.id" : [ [ "543534" , "543534" ] ] } } ], "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "nscannedObjectsAllPlans" : 1, "nscannedAllPlans" : 1, "millis" : 0, } 5) Incapsulate the or inside an other $or or $and db.testindex.find({ "user" : "mario" , "removed" : false , "$and" : [ { "$or" : [ { "objId" : "299939" } , { "p.id" : "543534" }]} ]} ).explain() { "cursor" : "BtreeCursor user_1_removed_1__id_1" , "isMultiKey" : false , "n" : 1, "nscannedObjects" : 5, "nscanned" : 5, "nscannedObjectsAllPlans" : 5, "nscannedAllPlans" : 5, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "user" : [ [ "mario" , "mario" ] ], "removed" : [ [ false , false ] ], "_id" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, } Mongodb is not able to rewrite the nested $or into the canonical rooted $or form, thus can't make use of the index union.

1) db.testindex.find({"user":"mario","removed":false, "$or" : [ { "objId" : "299939"} , { "p.id" : "543534"}]})

2) db.testindex.find({"user":"mario","removed":false,
"$and" : [ {"$or" : [ { "objId" : "299939"} , { "p.id" : "543534"}]} ]})

3) db.testindex.find({"user":"mario","removed":false,
"$or" : [ {"$or" : [ { "objId" : "299939"} , { "p.id" : "543534"}]} ]})

These three queries are semantically the same, but only the 1st can perform over the indexes user_1_removed_1_objId_1 and user_1_removed_1_p.id_1

The nested $and / $or operators block the mongodb query rewrite optimization.

duplicates

SERVER-14130 Query planner could consider additional query plans for $or

Closed

is related to

SERVER-13732 Predicates in top-level implicit AND query not considered when generating index access plan for contained OR

Closed

SERVER-12594 subtrees of and/or can reduce to ixscans

Backlog

Details

Description

Attachments

Issue Links

Activity

People

Dates