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

Index not used for query on capped collection using reverse $natural sort order

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • 2.6.4, 3.2.1
    • Querying
    • None
    • ALL
    • Hide

      Setup and query

      use test
      db.createCollection("tcapped", {capped:true, size: 10000000})
      db.tcapped.ensureIndex({num: 1})
      db.tcapped.insert({num: 1})
      db.tcapped.find({num: 1}).sort({$natural: -1}).explain()
      

      Version 2.6.4 result

      /* 0 */
      {
          "cursor" : "BasicCursor",
          "isMultiKey" : false,
          "n" : 1,
          "nscannedObjects" : 1,
          "nscanned" : 1,
          "nscannedObjectsAllPlans" : 1,
          "nscannedAllPlans" : 1,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nYields" : 0,
          "nChunkSkips" : 0,
          "millis" : 0,
          "server" : "alfie-gb:27017",
          "filterSet" : false,
          "stats" : {
              "type" : "COLLSCAN",
              "works" : 3,
              "yields" : 0,
              "unyields" : 0,
              "invalidates" : 0,
              "advanced" : 1,
              "needTime" : 1,
              "needFetch" : 0,
              "isEOF" : 1,
              "docsTested" : 1,
              "children" : []
          }
      }
      

      Version 3.2.1 result

      /* 0 */
      {
          "queryPlanner" : {
              "plannerVersion" : 1,
              "namespace" : "test.tcapped",
              "indexFilterSet" : false,
              "parsedQuery" : {
                  "num" : {
                      "$eq" : 1
                  }
              },
              "winningPlan" : {
                  "stage" : "COLLSCAN",
                  "filter" : {
                      "num" : {
                          "$eq" : 1
                      }
                  },
                  "direction" : "backward"
              },
              "rejectedPlans" : []
          },
          "executionStats" : {
              "executionSuccess" : true,
              "nReturned" : 1,
              "executionTimeMillis" : 0,
              "totalKeysExamined" : 0,
              "totalDocsExamined" : 1,
              "executionStages" : {
                  "stage" : "COLLSCAN",
                  "filter" : {
                      "num" : {
                          "$eq" : 1
                      }
                  },
                  "nReturned" : 1,
                  "executionTimeMillisEstimate" : 0,
                  "works" : 3,
                  "advanced" : 1,
                  "needTime" : 1,
                  "needYield" : 0,
                  "saveState" : 0,
                  "restoreState" : 0,
                  "isEOF" : 1,
                  "invalidates" : 0,
                  "direction" : "backward",
                  "docsExamined" : 1
              },
              "allPlansExecution" : []
          },
          "serverInfo" : {
              "host" : "alfie-gb",
              "port" : 27017,
              "version" : "3.2.1",
              "gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
          }
      }
      

      Show
      Setup and query use test db.createCollection("tcapped", {capped:true, size: 10000000}) db.tcapped.ensureIndex({num: 1}) db.tcapped.insert({num: 1}) db.tcapped.find({num: 1}).sort({$natural: -1}).explain() Version 2.6.4 result /* 0 */ { "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "nscannedObjectsAllPlans" : 1, "nscannedAllPlans" : 1, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "server" : "alfie-gb:27017", "filterSet" : false, "stats" : { "type" : "COLLSCAN", "works" : 3, "yields" : 0, "unyields" : 0, "invalidates" : 0, "advanced" : 1, "needTime" : 1, "needFetch" : 0, "isEOF" : 1, "docsTested" : 1, "children" : [] } } Version 3.2.1 result /* 0 */ { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.tcapped", "indexFilterSet" : false, "parsedQuery" : { "num" : { "$eq" : 1 } }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "num" : { "$eq" : 1 } }, "direction" : "backward" }, "rejectedPlans" : [] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 1, "executionStages" : { "stage" : "COLLSCAN", "filter" : { "num" : { "$eq" : 1 } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 1, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "direction" : "backward", "docsExamined" : 1 }, "allPlansExecution" : [] }, "serverInfo" : { "host" : "alfie-gb", "port" : 27017, "version" : "3.2.1", "gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2" } }

    Description

      If sort({$natural: -1}) is specified on a capped collection query which would normally use an index scan, the query switches to a colscan. For a large collection this causes performance issues.

      For our use case it's important to be able to query a capped collection using an index, limit the result count and iterate backwards based on insertion order, latest first.

      Tested with 2.6.4 and 3.2.1 (WiredTiger storage engine).

      I didn't test with a non-capped collection.

      Attachments

        Activity

          People

            kelsey.schubert@mongodb.com Kelsey Schubert
            jugglingcats Alfie Kirkpatrick
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: