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

Avoid performing full scans of multikey indices when a SORT stage is required

    • Type: Icon: Improvement Improvement
    • Resolution: Gone away
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • QO 2022-02-07

      Due to the changes in SERVER-19402, it is no longer correct for a multikey index scan to provide a non-blocking sort. Therefore, in general, a multikey index should not be used for operations if it is only eligible for consideration based on the requested sort.

      This appears to already be the case for aggregations as a COLLSCAN is performed:

      > db.demo.explain().aggregate([{$sort:{x:1}}])
      {
      	"stages" : [
      		{
      			"$cursor" : {
      				"query" : {
      					
      				},
      				"queryPlanner" : {
      					"plannerVersion" : 1,
      					"namespace" : "test.demo",
      					"indexFilterSet" : false,
      					"parsedQuery" : {
      						
      					},
      					"queryHash" : "8B3D4AB8",
      					"winningPlan" : {
      						"stage" : "COLLSCAN",
      						"direction" : "forward"
      					},
      					"rejectedPlans" : [ ]
      				}
      			}
      		},
      		{
      			"$sort" : {
      				"sortKey" : {
      					"x" : 1
      				}
      			}
      		}
      	],
      	"ok" : 1
      }
      

      However find commands appear to generate a plan with "[MinKey, MaxKey]"  index bounds:

      > db.demo.explain().find().sort({x:1})
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.demo",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			
      		},
      		"queryHash" : "B7791BAD",
      		"winningPlan" : {
      			"stage" : "SORT",
      			"sortPattern" : {
      				"x" : 1
      			},
      			"inputStage" : {
      				"stage" : "SORT_KEY_GENERATOR",
      				"inputStage" : {
      					"stage" : "FETCH",
      					"inputStage" : {
      						"stage" : "IXSCAN",
      						"keyPattern" : {
      							"x" : 1
      						},
      						"indexName" : "x_1",
      						"isMultiKey" : true,
      						"multiKeyPaths" : {
      							"x" : [
      								"x"
      							]
      						},
      						"isUnique" : false,
      						"isSparse" : false,
      						"isPartial" : false,
      						"indexVersion" : 2,
      						"direction" : "forward",
      						"indexBounds" : {
      							"x" : [
      								"[MinKey, MaxKey]"
      							]
      						}
      					}
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "Chriss-MacBook-Pro.local",
      		"port" : 27017,
      		"version" : "4.1.4",
      		"gitVersion" : "2f4b5918497b09a226a3ec5dcff930edd52ea1e9"
      	},
      	"ok" : 1
      }
      

            Assignee:
            timour.katchaounov@mongodb.com Timour Katchaounov
            Reporter:
            christopher.harris@mongodb.com Chris Harris
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: