Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-14740

Query rewrite of special $or leaf case to rooted $or not working for nested expressions

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Trivial - P5 Trivial - P5
    • None
    • Affects Version/s: 2.4.2
    • Component/s: Index Maintenance, Querying
    • None
    • Linux
    • 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.

            Assignee:
            Unassigned Unassigned
            Reporter:
            flavio@alicubi.net flavio alberti
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: