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

Project stage prevents sort stage from using index

    • Query
    • ALL
    • Hide

      MongoDB version is 4.1.5

      > db.version()
      4.1.5
      

      Create base collection, and its index.

      > db.base.createIndex({x:1})
      {
              "createdCollectionAutomatically" : true,
              "numIndexesBefore" : 1,
              "numIndexesAfter" : 2,
              "ok" : 1
      }
      

      Explain sort query on base collection. It uses IXSCAN.

      > db.base.find({}).sort({x:1}).explain()
      {
              "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "test.base",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
      
                      },
                      "queryHash" : "B7791BAD",
                      "planCacheKey" : "B7791BAD",
                      "winningPlan" : {
                              "stage" : "FETCH",
                              "inputStage" : {
                                      "stage" : "IXSCAN",
                                      "keyPattern" : {
                                              "x" : 1
                                      },
                                      "indexName" : "x_1",
                                      "isMultiKey" : false,
                                      "multiKeyPaths" : {
                                              "x" : [ ]
                                      },
                                      "isUnique" : false,
                                      "isSparse" : false,
                                      "isPartial" : false,
                                      "indexVersion" : 2,
                                      "direction" : "forward",
                                      "indexBounds" : {
                                              "x" : [
                                                      "[MinKey, MaxKey]"
                                              ]
                                      }
                              }
                      },
                      "rejectedPlans" : [ ]
              },
              "serverInfo" : {
                      "host" : "8ab9c3a1df86",
                      "port" : 27017,
                      "version" : "4.1.5",
                      "gitVersion" : "f3349bac21f200cf2f9854eb51b359d3cbee3617"
              },
              "ok" : 1
      }
      

      Create a view based on base collection

      > db.createView("view", "base", [{$project:{x:1}}])
      { "ok" : 1 }
      

      Explain sort query on view collection. It uses COLLSCAN.

      > db.view.find({}).sort({x:1}).explain()
      {
              "stages" : [
                      {
                              "$cursor" : {
                                      "query" : {
      
                                      },
                                      "fields" : {
                                              "x" : 1,
                                              "_id" : 1
                                      },
                                      "queryPlanner" : {
                                              "plannerVersion" : 1,
                                              "namespace" : "test.base",
                                              "indexFilterSet" : false,
                                              "parsedQuery" : {
      
                                              },
                                              "queryHash" : "8B3D4AB8",
                                              "planCacheKey" : "8B3D4AB8",
                                              "winningPlan" : {
                                                      "stage" : "COLLSCAN",
                                                      "direction" : "forward"
                                              },
                                              "rejectedPlans" : [ ]
                                      }
                              }
                      },
                      {
                              "$project" : {
                                      "_id" : true,
                                      "x" : true
                              }
                      },
                      {
                              "$sort" : {
                                      "sortKey" : {
                                              "x" : 1
                                      }
                              }
                      }
              ],
              "ok" : 1
      }
      
      Show
      MongoDB version is 4.1.5 > db.version() 4.1.5 Create base collection, and its index. > db.base.createIndex({x:1}) { "createdCollectionAutomatically" : true , "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } Explain sort query on base collection. It uses IXSCAN. > db.base.find({}).sort({x:1}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.base" , "indexFilterSet" : false , "parsedQuery" : { }, "queryHash" : "B7791BAD" , "planCacheKey" : "B7791BAD" , "winningPlan" : { "stage" : "FETCH" , "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "x" : 1 }, "indexName" : "x_1" , "isMultiKey" : false , "multiKeyPaths" : { "x" : [ ] }, "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : 2, "direction" : "forward" , "indexBounds" : { "x" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "8ab9c3a1df86" , "port" : 27017, "version" : "4.1.5" , "gitVersion" : "f3349bac21f200cf2f9854eb51b359d3cbee3617" }, "ok" : 1 } Create a view based on base collection > db.createView( "view" , "base" , [{$project:{x:1}}]) { "ok" : 1 } Explain sort query on view collection. It uses COLLSCAN. > db.view.find({}).sort({x:1}).explain() { "stages" : [ { "$cursor" : { "query" : { }, "fields" : { "x" : 1, "_id" : 1 }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.base" , "indexFilterSet" : false , "parsedQuery" : { }, "queryHash" : "8B3D4AB8" , "planCacheKey" : "8B3D4AB8" , "winningPlan" : { "stage" : "COLLSCAN" , "direction" : "forward" }, "rejectedPlans" : [ ] } } }, { "$project" : { "_id" : true , "x" : true } }, { "$sort" : { "sortKey" : { "x" : 1 } } } ], "ok" : 1 }

      View cannot use underlying collection index properly for sort.

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            czchen ChangZhuo Chen
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: