[SERVER-33387] Multikey index does not provide nonblocking sort in MongoDB 3.6 Created: 19/Feb/18  Updated: 27/Oct/23  Resolved: 20/Feb/18

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.6.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: PRAVEEN SUBBA RAO Assignee: Kelsey Schubert
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to DOCS-11338 Add multikey index sort limitation to... Closed
related to SERVER-19402 Change semantics of sorting by array ... Closed
related to SERVER-31898 Improve sort analysis to allow multik... Closed
Operating System: ALL
Steps To Reproduce:

1. Create a collection with a huge number of records with the fields (dd, refKey, hashSum, _id)
2. Create an index on (dd:1, refKey:1, hashSum:1, _id:1)
3. Execute this query:

 db.customer.find({"dd": "com.x"}).sort({"dd":1, "refKey":1, "hashSum":1, "_id":1})

Participants:

 Description   
Original Summary

MongoDB 3.6.2 not using the index for sort where the prefix key of the index appears in both the query predicate and the sort.

Original Description

MongoDB 3.6.2 not using the index for sort where the prefix key of the index appears in both the query predicate and the sort.
We have a collection called customer with 316432 records and using mongoDB 3.6.2.

Trying to execute the query:

db.customer.find({"dd": "com.x"}).sort({"dd":1, "refKey":1, "hashSum":1, "_id":1})

where we have an index defined as:

{"dd":1, "refKey":1, "hashSum":1, "_id":1}

MongoDB doesn't use the index and tries to do the sort in memory and hits the 32MB limit.

Error: error: {
	"ok" : 0,
	"errmsg" : "errmsg: \"Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.\"",
	"code" : 96,
	"codeName" : "OperationFailed",
	"operationTime" : Timestamp(1519024594, 1)
}

Request your help in this regard since it is very urgent and blocking.



 Comments   
Comment by Kelsey Schubert [ 20/Feb/18 ]

Yup, that's right.

Comment by PRAVEEN SUBBA RAO [ 20/Feb/18 ]

Hi Kelsey, if I understood what you were saying - since the "dataDomain" field is an array in our application, the SORT stage was blocking.
I guess we can rectify this in our application to not sort on that particular field.
Thanks for the help.

Comment by Kelsey Schubert [ 20/Feb/18 ]

Hi praveensrao,

Thanks for the additional information. From the explain output, I can see that the index is multikey and dataDomain is the multikey path. In MongoDB 3.6, SERVER-19402 was resolved, which changed sort mechanics to ensure they were consistent across all access patterns. Unfortunately, to achieve this consistency, it is no longer correct for a multikey index to be used to provide a sort on an array field. Please see SERVER-19402 and our release notes on compatibility changes for more details has a detailed summary of this issue and its consequences.

To work around this issue, I would suggest considering whether its feasible to modify your application layer to remove the sort on dataDomain, e.g. only sort on {"refKey":1, "hashSum":1, "_id":1}, as this would enable your current index to provide a non-blocking sort.

For your reference, I've opened DOCS-11338 to improve our documentation and improvements to this behavior are planned in SERVER-31898. However, I don't expect SERVER-31898 to help this particular access pattern.

Kind regards,
Kelsey

Comment by PRAVEEN SUBBA RAO [ 19/Feb/18 ]

I am sorry on the formatting, will make it a point henceforth to format the data for code / shell output before adding it in the comments.

Comment by PRAVEEN SUBBA RAO [ 19/Feb/18 ]

Kelsey, request you to please treat this as high priority since it is preventing us from migrating to mongodb 3.6.2 and we wanted to move to it because of other performance issues in 3.4.x
Thanks for understanding.

Comment by PRAVEEN SUBBA RAO [ 19/Feb/18 ]

Hi Kelsey, thanks for the response.

db.customer.getIndexes()
Have this index along with a couple of other indexes:

	{
		"v" : 2,
		"key" : {
			"dataDomain" : 1,
			"refKey" : 1,
			"hashSum" : 1,
			"_id" : 1
		},
		"name" : "idx_dd_rk_hs_id",
		"ns" : "app-customerDB.customer",
		"background" : true
	}

I am not able to edit the original description.
Substitute "dd" with "dataDomain" and "com.x" with "com.pdc" please

db.customer.find({"dataDomain":"com.pdc"}).sort({"dataDomain": 1, "refKey": 1, "hashSum": 1, "_id": 1})
Error: error: {
	"ok" : 0,
	"errmsg" : "errmsg: \"Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.\"",
	"code" : 96,
	"codeName" : "OperationFailed",
	"operationTime" : Timestamp(1519073916, 1)
}

Unfortunately not able to get an explain plan with the same issue:

db.customer.find({"dataDomain":"com.pdc"}).sort({"dataDomain": 1, "refKey": 1, "hashSum": 1, "_id": 1}).explain(true)
2018-02-19T15:59:40.534-0500 E QUERY    [thread1] Error: explain failed: {
	"ok" : 0,
	"errmsg" : "errmsg: \"Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.\"",
	"code" : 96,
	"codeName" : "OperationFailed",
	"operationTime" : Timestamp(1519073976, 1)
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
throwOrReturn@src/mongo/shell/explainable.js:31:1
constructor/this.finish@src/mongo/shell/explain_query.js:172:24
DBQuery.prototype.explain@src/mongo/shell/query.js:521:12
@(shell):1:1

Most of the records are with "dataDomain" = "com.pdc"

However if i try to do an explain plan with "dataDomain" as "com.y"
then this is the result:

db.customer.find({"dataDomain":"com.y"}).sort({"dataDomain": 1, "refKey": 1, "hashSum": 1, "_id": 1}).explain(true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "app-customerDB.customer",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"dataDomain" : {
				"$eq" : "com.y"
			}
		},
		"winningPlan" : {
			"stage" : "SORT",
			"sortPattern" : {
				"dataDomain" : 1,
				"refKey" : 1,
				"hashSum" : 1,
				"_id" : 1
			},
			"inputStage" : {
				"stage" : "SORT_KEY_GENERATOR",
				"inputStage" : {
					"stage" : "FETCH",
					"inputStage" : {
						"stage" : "IXSCAN",
						"keyPattern" : {
							"dataDomain" : 1,
							"refKey" : 1,
							"hashSum" : 1,
							"_id" : 1
						},
						"indexName" : "idx_dd_rk_hs_id",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"dataDomain" : [
								"dataDomain"
							],
							"refKey" : [ ],
							"hashSum" : [ ],
							"_id" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"dataDomain" : [
								"[\"com.y\", \"com.y\"]"
							],
							"refKey" : [
								"[MinKey, MaxKey]"
							],
							"hashSum" : [
								"[MinKey, MaxKey]"
							],
							"_id" : [
								"[MinKey, MaxKey]"
							]
						}
					}
				}
			}
		},
		"rejectedPlans" : [
			{
				"stage" : "SORT",
				"sortPattern" : {
					"dataDomain" : 1,
					"refKey" : 1,
					"hashSum" : 1,
					"_id" : 1
				},
				"inputStage" : {
					"stage" : "SORT_KEY_GENERATOR",
					"inputStage" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"dataDomain" : 1,
								"refName" : 1
							},
							"indexName" : "dataDomain_1_refName_1",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"dataDomain" : [
									"dataDomain"
								],
								"refName" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"dataDomain" : [
									"[\"com.y\", \"com.y\"]"
								],
								"refName" : [
									"[MinKey, MaxKey]"
								]
							}
						}
					}
				}
			},
			{
				"stage" : "SORT",
				"sortPattern" : {
					"dataDomain" : 1,
					"refKey" : 1,
					"hashSum" : 1,
					"_id" : 1
				},
				"inputStage" : {
					"stage" : "SORT_KEY_GENERATOR",
					"inputStage" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"dataDomain" : 1,
								"customerNumber" : 1
							},
							"indexName" : "dataDomain_1_customerNumber_1",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"dataDomain" : [
									"dataDomain"
								],
								"customerNumber" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"dataDomain" : [
									"[\"com.y\", \"com.y\"]"
								],
								"customerNumber" : [
									"[MinKey, MaxKey]"
								]
							}
						}
					}
				}
			},
			{
				"stage" : "SORT",
				"sortPattern" : {
					"dataDomain" : 1,
					"refKey" : 1,
					"hashSum" : 1,
					"_id" : 1
				},
				"inputStage" : {
					"stage" : "SORT_KEY_GENERATOR",
					"inputStage" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"dataDomain" : 1,
								"name.lastName" : -1
							},
							"indexName" : "idx_dd_nm_lst",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"dataDomain" : [
									"dataDomain"
								],
								"name.lastName" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"dataDomain" : [
									"[\"com.y\", \"com.y\"]"
								],
								"name.lastName" : [
									"[MaxKey, MinKey]"
								]
							}
						}
					}
				}
			},
			{
				"stage" : "SORT",
				"sortPattern" : {
					"dataDomain" : 1,
					"refKey" : 1,
					"hashSum" : 1,
					"_id" : 1
				},
				"inputStage" : {
					"stage" : "SORT_KEY_GENERATOR",
					"inputStage" : {
						"stage" : "FETCH",
						"filter" : {
							"dataDomain" : {
								"$eq" : "com.y"
							}
						},
							"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"dataDomain" : 1,
								"refKey" : 1,
								"hashSum" : 1,
								"_id" : 1
							},
							"indexName" : "idx_dd_rk_hs_id",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"dataDomain" : [
									"dataDomain"
								],
								"refKey" : [ ],
								"hashSum" : [ ],
								"_id" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"dataDomain" : [
									"[MinKey, MaxKey]"
								],
								"refKey" : [
									"[MinKey, MaxKey]"
								],
								"hashSum" : [
									"[MinKey, MaxKey]"
								],
								"_id" : [
									"[MinKey, MaxKey]"
								]
							}
						}
					}
				}
			}
		]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 0,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 0,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "SORT",
			"nReturned" : 0,
			"executionTimeMillisEstimate" : 0,
			"works" : 4,
			"advanced" : 0,
			"needTime" : 2,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"sortPattern" : {
				"dataDomain" : 1,
				"refKey" : 1,
				"hashSum" : 1,
				"_id" : 1
			},
			"memUsage" : 0,
			"memLimit" : 33554432,
			"inputStage" : {
				"stage" : "SORT_KEY_GENERATOR",
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"works" : 2,
				"advanced" : 0,
				"needTime" : 1,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"inputStage" : {
					"stage" : "FETCH",
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 1,
					"advanced" : 0,
					"needTime" : 0,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"docsExamined" : 0,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 1,
						"advanced" : 0,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"keyPattern" : {
							"dataDomain" : 1,
							"refKey" : 1,
							"hashSum" : 1,
							"_id" : 1
						},
						"indexName" : "idx_dd_rk_hs_id",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"dataDomain" : [
								"dataDomain"
							],
							"refKey" : [ ],
							"hashSum" : [ ],
							"_id" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"dataDomain" : [
								"[\"com.y\", \"com.y\"]"
							],
							"refKey" : [
								"[MinKey, MaxKey]"
							],
							"hashSum" : [
								"[MinKey, MaxKey]"
							],
							"_id" : [
								"[MinKey, MaxKey]"
							]
						},
						"keysExamined" : 0,
						"seeks" : 1,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0
					}
				}
			}
		},
		"allPlansExecution" : [
			{
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"totalKeysExamined" : 0,
				"totalDocsExamined" : 0,
				"executionStages" : {
					"stage" : "SORT",
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 3,
					"advanced" : 0,
					"needTime" : 2,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"sortPattern" : {
						"dataDomain" : 1,
						"refKey" : 1,
						"hashSum" : 1,
						"_id" : 1
					},
					"memUsage" : 0,
					"memLimit" : 33554432,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 2,
						"advanced" : 0,
						"needTime" : 1,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"inputStage" : {
							"stage" : "FETCH",
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 0,
							"works" : 1,
							"advanced" : 0,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 1,
							"invalidates" : 0,
							"docsExamined" : 0,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 1,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 1,
								"invalidates" : 0,
								"keyPattern" : {
									"dataDomain" : 1,
									"refName" : 1
								},
								"indexName" : "dataDomain_1_refName_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"dataDomain" : [
										"dataDomain"
									],
									"refName" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"dataDomain" : [
										"[\"com.y\", \"com.y\"]"
									],
									"refName" : [
										"[MinKey, MaxKey]"
									]
								},
								"keysExamined" : 0,
								"seeks" : 1,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0
							}
						}
					}
				}
			},
			{
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"totalKeysExamined" : 0,
				"totalDocsExamined" : 0,
				"executionStages" : {
					"stage" : "SORT",
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 3,
					"advanced" : 0,
					"needTime" : 2,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"sortPattern" : {
						"dataDomain" : 1,
						"refKey" : 1,
						"hashSum" : 1,
						"_id" : 1
					},
					"memUsage" : 0,
					"memLimit" : 33554432,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 2,
						"advanced" : 0,
						"needTime" : 1,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"inputStage" : {
							"stage" : "FETCH",
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 0,
							"works" : 1,
							"advanced" : 0,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 1,
							"invalidates" : 0,
							"docsExamined" : 0,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 1,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 1,
								"invalidates" : 0,
								"keyPattern" : {
									"dataDomain" : 1,
									"customerNumber" : 1
								},
								"indexName" : "dataDomain_1_customerNumber_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"dataDomain" : [
										"dataDomain"
									],
									"customerNumber" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"dataDomain" : [
										"[\"com.y\", \"com.y\"]"
									],
									"customerNumber" : [
										"[MinKey, MaxKey]"
									]
								},
								"keysExamined" : 0,
								"seeks" : 1,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0
							}
						}
					}
				}
			},
			{
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"totalKeysExamined" : 0,
				"totalDocsExamined" : 0,
				"executionStages" : {
					"stage" : "SORT",
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 3,
					"advanced" : 0,
					"needTime" : 2,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"sortPattern" : {
						"dataDomain" : 1,
						"refKey" : 1,
						"hashSum" : 1,
						"_id" : 1
					},
					"memUsage" : 0,
					"memLimit" : 33554432,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 2,
						"advanced" : 0,
						"needTime" : 1,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"inputStage" : {
							"stage" : "FETCH",
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 0,
							"works" : 1,
							"advanced" : 0,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 1,
							"invalidates" : 0,
							"docsExamined" : 0,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 1,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 1,
								"invalidates" : 0,
								"keyPattern" : {
									"dataDomain" : 1,
									"name.lastName" : -1
								},
								"indexName" : "idx_dd_nm_lst",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"dataDomain" : [
										"dataDomain"
									],
									"name.lastName" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"dataDomain" : [
										"[\"com.y\", \"com.y\"]"
									],
									"name.lastName" : [
										"[MaxKey, MinKey]"
									]
								},
								"keysExamined" : 0,
								"seeks" : 1,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0
							}
						}
					}
				}
			},
			{
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"totalKeysExamined" : 2,
				"totalDocsExamined" : 2,
				"executionStages" : {
					"stage" : "SORT",
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 3,
					"advanced" : 0,
					"needTime" : 3,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 0,
					"invalidates" : 0,
					"sortPattern" : {
						"dataDomain" : 1,
						"refKey" : 1,
						"hashSum" : 1,
						"_id" : 1
					},
					"memUsage" : 0,
					"memLimit" : 33554432,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 3,
						"advanced" : 0,
						"needTime" : 3,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 0,
						"invalidates" : 0,
						"inputStage" : {
							"stage" : "FETCH",
							"filter" : {
								"dataDomain" : {
									"$eq" : "com.y"
								}
							},
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 0,
							"works" : 2,
							"advanced" : 0,
							"needTime" : 2,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 0,
							"invalidates" : 0,
							"docsExamined" : 2,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "IXSCAN",
								"nReturned" : 2,
								"executionTimeMillisEstimate" : 0,
								"works" : 2,
								"advanced" : 2,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 0,
								"invalidates" : 0,
								"keyPattern" : {
									"dataDomain" : 1,
									"refKey" : 1,
									"hashSum" : 1,
									"_id" : 1
								},
								"indexName" : "idx_dd_rk_hs_id",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"dataDomain" : [
										"dataDomain"
									],
									"refKey" : [ ],
									"hashSum" : [ ],
									"_id" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"dataDomain" : [
										"[MinKey, MaxKey]"
									],
									"refKey" : [
										"[MinKey, MaxKey]"
									],
									"hashSum" : [
										"[MinKey, MaxKey]"
									],
									"_id" : [
										"[MinKey, MaxKey]"
									]
								},
								"keysExamined" : 2,
								"seeks" : 1,
								"dupsTested" : 2,
								"dupsDropped" : 0,
								"seenInvalidated" : 0
							}
						}
					}
				}
			},
			{
				"nReturned" : 0,
				"executionTimeMillisEstimate" : 0,
				"totalKeysExamined" : 0,
				"totalDocsExamined" : 0,
				"executionStages" : {
					"stage" : "SORT",
					"nReturned" : 0,
					"executionTimeMillisEstimate" : 0,
					"works" : 3,
					"advanced" : 0,
					"needTime" : 2,
					"needYield" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"sortPattern" : {
						"dataDomain" : 1,
						"refKey" : 1,
						"hashSum" : 1,
						"_id" : 1
					},
					"memUsage" : 0,
					"memLimit" : 33554432,
					"inputStage" : {
						"stage" : "SORT_KEY_GENERATOR",
						"nReturned" : 0,
						"executionTimeMillisEstimate" : 0,
						"works" : 2,
						"advanced" : 0,
						"needTime" : 1,
						"needYield" : 0,
						"saveState" : 0,
						"restoreState" : 0,
						"isEOF" : 1,
						"invalidates" : 0,
						"inputStage" : {
							"stage" : "FETCH",
							"nReturned" : 0,
							"executionTimeMillisEstimate" : 0,
							"works" : 1,
							"advanced" : 0,
							"needTime" : 0,
							"needYield" : 0,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 1,
							"invalidates" : 0,
							"docsExamined" : 0,
							"alreadyHasObj" : 0,
							"inputStage" : {
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 1,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 0,
								"restoreState" : 0,
								"isEOF" : 1,
								"invalidates" : 0,
								"keyPattern" : {
									"dataDomain" : 1,
									"refKey" : 1,
									"hashSum" : 1,
									"_id" : 1
								},
								"indexName" : "idx_dd_rk_hs_id",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"dataDomain" : [
										"dataDomain"
									],
									"refKey" : [ ],
									"hashSum" : [ ],
									"_id" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"dataDomain" : [
										"[\"com.y\", \"com.y\"]"
									],
									"refKey" : [
										"[MinKey, MaxKey]"
									],
									"hashSum" : [
										"[MinKey, MaxKey]"
									],
									"_id" : [
										"[MinKey, MaxKey]"
									]
								},
								"keysExamined" : 0,
								"seeks" : 1,
								"dupsTested" : 0,
								"dupsDropped" : 0,
								"seenInvalidated" : 0
							}
						}
					}
				}
			}
		]
	},
	"serverInfo" : {
		"host" : "qa36-shard-00-00-fpaqn.mongodb.net",
		"port" : 27017,
		"version" : "3.6.2",
		"gitVersion" : "489d177dbd0f0420a8ca04d39fd78d0a2c539420"
	},
	"ok" : 1,
	"operationTime" : Timestamp(1519074126, 1)
}

Comment by Kelsey Schubert [ 19/Feb/18 ]

Hi praveensrao,

Thanks for the report. So we can investigate, would you please provide the complete output of the following commands:

 db.customer.getIndexes() 

 db.customer.find({"dd": "com.x"}).sort({"dd":1, "refKey":1, "hashSum":1, "_id":1}).explain(true) 

Kind regards,
Kelsey

Generated at Thu Feb 08 04:33:13 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.