Details
-
Task
-
Resolution: Fixed
-
Minor - P4
-
None
-
3
Description
On the Multikey Index page, at the bottom of the page we provide an example of using an index to support sort operations on a field containing an array of documents.
For the inventory, collection we suggest the following index:
db.inventory.createIndex( { "stock.size": 1, "stock.quantity": 1 } ) |
Further down the page then we say:
The compound multikey index can also support sort operations, such as the following examples:
|
One of these is:
db.inventory.find( { "stock.size": "M" } ).sort( { "stock.quantity": 1 } )
|
However this doesn't meet one of the restrictions for using an index to sort a multikey field:
No boundaries for any multikey-indexed field have the same path prefix as the sort pattern.
|
If you run the query using the data and index from the page and perform explain, there is a blocking SORT:
{
|
"explainVersion" : "1",
|
"queryPlanner" : {
|
"namespace" : "test.inventory",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"stock.size" : {
|
"$eq" : "M"
|
}
|
},
|
"queryHash" : "E69DB46D",
|
"planCacheKey" : "DEC46494",
|
"maxIndexedOrSolutionsReached" : false,
|
"maxIndexedAndSolutionsReached" : false,
|
"maxScansToExplodeReached" : false,
|
"winningPlan" : {
|
"stage" : "SORT",
|
"sortPattern" : {
|
"stock.quantity" : 1
|
},
|
"memLimit" : 104857600,
|
"type" : "simple",
|
"inputStage" : {
|
"stage" : "FETCH",
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"stock.size" : 1,
|
"stock.quantity" : 1
|
},
|
"indexName" : "stock.size_1_stock.quantity_1",
|
"isMultiKey" : true,
|
"multiKeyPaths" : {
|
"stock.size" : [
|
"stock"
|
],
|
"stock.quantity" : [
|
"stock"
|
]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"stock.size" : [
|
"[\"M\", \"M\"]"
|
],
|
"stock.quantity" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
},
|
This is discussed on SERVER-31898 (See constraint 2) and describes how a similar query shape would result in an incorrect sort, which is why it is not supported.
I believe that we should remove this example as it cannot use an index to SORT.