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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Trivial - P5
    • Resolution: Duplicate
    • Affects Version/s: 2.4.2
    • Fix Version/s: None
    • Component/s: Indexing, 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.

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: