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

Since 2.6, full collection scan instead of index scan, on range after sort

    • ALL
    • Hide
      Steps :
      1. Install docker :
        curl -fsSL get.docker.com -o get-docker.sh
        sh get-docker.sh
      2. (cli 1) Start a standalone mongod v2.2 :
        docker run --rm --name some-mongo mongo:2.2 --smallfiles --nojournal
      3. (cli 2) Open a mongo shell to this mongod :
        docker exec -it some-mongo "mongo"
      4. (cli 2) Create index, data, and find explain :
        db.createCollection('testIndex')
        db.testIndex.ensureIndex({a:1,b:1,c:1})
        for(var i=1;i++<500;){db.testIndex.insert({a:i,b:4,c:i+5});}
        for(var i=1;i++<500;){db.testIndex.insert({a:i,b:6,c:i+5});}
        db.testIndex.find({c:{$lt:100}}).sort({a:1,b:1}).explain()       # do an explain("executionStats") instead since v3.0 #
        
      5. (cli 1) Stop and remove this mongod with a [Ctrl]+[C]
      6. Start again at step 2, with each mongod version ("mongo:2.4", "mongo:2.6", "mongo:3.0", "mongo:3.2", "mongo:3.4", "mongo:3.5", etc).
      7. Optionally, clean un-used docker images to free disk space :
        docker rmi $(docker images -q)
      Results :
      mongod version totalKeysExamined (nscanned) totalDocsExamined (nscannedObjects) nReturned (n)
      2.2.7 942 186 186
      2.4.14 942 186 186
      2.6.12 998 998 186
      3.0.15 998 998 186
      3.2.16 998 998 186
      3.4.9 998 998 186
      3.5.13 998 998 186
      More details :
      • In 2.2 and 2.4, "indexBounds" looks something like this :
        "indexBounds" : {
            "a" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
            "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
            "c" : [ [ -1.7976931348623157e+308, 100 ] ]
        },
        
      • But in 2.6 an above :
        "indexBounds" : {
            "a" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
            "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
            "c" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ]
        },
        
      Show
      Steps : Install docker : curl -fsSL get.docker.com -o get-docker.sh sh get-docker.sh (cli 1) Start a standalone mongod v2.2 : docker run --rm --name some-mongo mongo:2.2 --smallfiles --nojournal (cli 2) Open a mongo shell to this mongod : docker exec -it some-mongo "mongo" (cli 2) Create index, data, and find explain : db.createCollection( 'testIndex' ) db.testIndex.ensureIndex({a:1,b:1,c:1}) for ( var i=1;i++<500;){db.testIndex.insert({a:i,b:4,c:i+5});} for ( var i=1;i++<500;){db.testIndex.insert({a:i,b:6,c:i+5});} db.testIndex.find({c:{$lt:100}}).sort({a:1,b:1}).explain() # do an explain( "executionStats" ) instead since v3.0 # (cli 1) Stop and remove this mongod with a [Ctrl]+[C] Start again at step 2, with each mongod version ("mongo:2.4", "mongo:2.6", "mongo:3.0", "mongo:3.2", "mongo:3.4", "mongo:3.5", etc). Optionally, clean un-used docker images to free disk space : docker rmi $(docker images -q) Results : mongod version totalKeysExamined (nscanned) totalDocsExamined (nscannedObjects) nReturned (n) 2.2.7 942 186 186 2.4.14 942 186 186 2.6.12 998 998 186 3.0.15 998 998 186 3.2.16 998 998 186 3.4.9 998 998 186 3.5.13 998 998 186 More details : In 2.2 and 2.4, "indexBounds" looks something like this : "indexBounds" : { "a" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ], "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ], "c" : [ [ -1.7976931348623157e+308, 100 ] ] }, But in 2.6 an above : "indexBounds" : { "a" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ], "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ], "c" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] },

      Hello,

      index {a:1,b:1,c:1}
      find( {c:{$lt:100}} ).sort( {a:1,b:1} )

      Before 2.6 : all matching docs are found from index (probably by doing an index scan of part "c" ?)
      From 2.6 and above : index return docs in sorted order, but a full document scan is required to check "c".

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            clement@compilatio Clement R
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: