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

Explicit rooted $and containing $or doesn't use index in 2.4

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Won't Fix
    • Icon: Major - P3 Major - P3
    • None
    • 2.4.13
    • Querying
    • None
    • ALL
    • Hide

      db.andor.drop()
      db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
      db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
      db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()
      

      The query with the explict rooted $and will use a BasicCursor, whereas without it uses the index as expected.

      Show
      db.andor.drop() db.andor.ensureIndex( { a: 1, b: 1, c: 1 } ) db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain() db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain() The query with the explict rooted $and will use a BasicCursor, whereas without it uses the index as expected.

    Description

      In 2.4, a query of the form { $and: [ { $or: [ ... ] } ] } does not choose an available suitable index:

      > db.version()
      2.4.13
      > db.andor.drop()
      false
      > db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
      Cannot use commands write mode, degrading to compatibility mode
      { "ok" : 1 }
      > db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
      {
              "cursor" : "BasicCursor",
              "isMultiKey" : false,
              "n" : 0,
              "nscannedObjects" : 0,
              "nscanned" : 0,
              "nscannedObjectsAllPlans" : 0,
              "nscannedAllPlans" : 0,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 0,
              "indexBounds" : {
              },
              "server" : "genique:11111"
      }
      > db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()
      {
              "clauses" : [
                      {
                              "cursor" : "BtreeCursor a_1_b_1_c_1",
                              "isMultiKey" : false,
                              "n" : 0,
                              "nscannedObjects" : 0,
                              "nscanned" : 0,
                              "nscannedObjectsAllPlans" : 0,
                              "nscannedAllPlans" : 0,
                              "scanAndOrder" : false,
                              "indexOnly" : false,
                              "nYields" : 0,
                              "nChunkSkips" : 0,
                              "millis" : 0,
                              "indexBounds" : {
                                      "a" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ],
                                      "b" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ],
                                      "c" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ]
                              }
                      },
                      {
                              "cursor" : "BtreeCursor a_1_b_1_c_1",
                              "isMultiKey" : false,
                              "n" : 0,
                              "nscannedObjects" : 0,
                              "nscanned" : 0,
                              "nscannedObjectsAllPlans" : 0,
                              "nscannedAllPlans" : 0,
                              "scanAndOrder" : false,
                              "indexOnly" : false,
                              "nYields" : 0,
                              "nChunkSkips" : 0,
                              "millis" : 0,
                              "indexBounds" : {
                                      "a" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ],
                                      "b" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ],
                                      "c" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ]
                              }
                      }
              ],
              "n" : 0,
              "nscannedObjects" : 0,
              "nscanned" : 0,
              "nscannedObjectsAllPlans" : 0,
              "nscannedAllPlans" : 0,
              "millis" : 0,
              "server" : "genique:11111"
      }
      

      Whereas in 2.6, the right index is selected as expected:

      > db.version()
      2.6.10
      > db.andor.drop()
      true
      > db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
      {
              "createdCollectionAutomatically" : true,
              "numIndexesBefore" : 1,
              "numIndexesAfter" : 2,
              "ok" : 1
      }
      > db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
      {
              "clauses" : [
                      {
                              "cursor" : "BtreeCursor a_1_b_1_c_1",
                              "isMultiKey" : false,
                              "n" : 0,
                              "nscannedObjects" : 0,
                              "nscanned" : 0,
                              "scanAndOrder" : false,
                              "indexOnly" : false,
                              "nChunkSkips" : 0,
                              "indexBounds" : {
                                      "a" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ],
                                      "b" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ],
                                      "c" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ]
                              }
                      },
                      {
                              "cursor" : "BtreeCursor a_1_b_1_c_1",
                              "isMultiKey" : false,
                              "n" : 0,
                              "nscannedObjects" : 0,
                              "nscanned" : 0,
                              "scanAndOrder" : false,
                              "indexOnly" : false,
                              "nChunkSkips" : 0,
                              "indexBounds" : {
                                      "a" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ],
                                      "b" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ],
                                      "c" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ]
                              }
                      }
              ],
              "cursor" : "QueryOptimizerCursor",
              "n" : 0,
              "nscannedObjects" : 0,
              "nscanned" : 0,
              "nscannedObjectsAllPlans" : 0,
              "nscannedAllPlans" : 0,
              "scanAndOrder" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 0,
              "server" : "genique:27017",
              "filterSet" : false
      }
      > db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()
      {
              "clauses" : [
                      {
                              "cursor" : "BtreeCursor a_1_b_1_c_1",
                              "isMultiKey" : false,
                              "n" : 0,
                              "nscannedObjects" : 0,
                              "nscanned" : 0,
                              "scanAndOrder" : false,
                              "indexOnly" : false,
                              "nChunkSkips" : 0,
                              "indexBounds" : {
                                      "a" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ],
                                      "b" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ],
                                      "c" : [
                                              [
                                                      1,
                                                      1
                                              ]
                                      ]
                              }
                      },
                      {
                              "cursor" : "BtreeCursor a_1_b_1_c_1",
                              "isMultiKey" : false,
                              "n" : 0,
                              "nscannedObjects" : 0,
                              "nscanned" : 0,
                              "scanAndOrder" : false,
                              "indexOnly" : false,
                              "nChunkSkips" : 0,
                              "indexBounds" : {
                                      "a" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ],
                                      "b" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ],
                                      "c" : [
                                              [
                                                      2,
                                                      2
                                              ]
                                      ]
                              }
                      }
              ],
              "cursor" : "QueryOptimizerCursor",
              "n" : 0,
              "nscannedObjects" : 0,
              "nscanned" : 0,
              "nscannedObjectsAllPlans" : 0,
              "nscannedAllPlans" : 0,
              "scanAndOrder" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 0,
              "server" : "genique:27017",
              "filterSet" : false
      }
      

      Attachments

        Activity

          People

            ramon.fernandez@mongodb.com Ramon Fernandez Marina
            kevin.pulo@mongodb.com Kevin Pulo
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: