[DOCS-15245] [Server] Multikey Index Page Example Issue Created: 14/Apr/22  Updated: 30/Oct/23  Resolved: 17/Jul/23

Status: Closed
Project: Documentation
Component/s: manual
Affects Version/s: None
Fix Version/s: Server_Docs_20231030

Type: Task Priority: Minor - P4
Reporter: Ronan Merrick Assignee: Kanchana Sekhar
Resolution: Fixed Votes: 0
Labels: bugfix, docs-onboarding-candidate, quick-win, server-docs-bug-bash
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:
Days since reply: 30 weeks ago
Story Points: 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.



 Comments   
Comment by Kanchana Sekhar [ 12/Jul/23 ]

PR: https://github.com/10gen/docs-mongodb-internal/pull/3726

Comment by Juan Ley [ 07/Jul/23 ]

I found the answer in the documentation https://www.mongodb.com/docs/v4.4/core/index-multikey/:

This is the sort behavior:
As a result of changes to sorting behavior on array fields in MongoDB 4.4, when you sort on an array indexed with a multikey index, the query plan includes a blocking sort stage, unless:

The index boundaries for all sort fields are [MinKey, MaxKey], and
No boundaries for any multikey-indexed field have the same path prefix as the sort pattern.
In the examples we have:

Query 1 does not have min/Max keys {db.test.find( ).sort(

{ "stock.size": 1, "stock.quantity": 1 }

)} and therefore no blocking sort.
Query 2 has min/Max key = "M" {db.test.find(

{ "stock.size": "M" }

).sort(

{ "stock.quantity": 1 }

)} and therefore it has a blocking sort.

Comment by Juan Ley [ 29/Jun/23 ]

Yes the documentation: https://www.mongodb.com/docs/manual/core/index-multikey/

has a mistake in the last example. Explain plan of Query with the data shows:

db.test.find(

{ "stock.size": "M" }

).sort(

{ "stock.quantity": 1 }

).explain("allPlansExecution")
{
queryPlanner: {
plannerVersion: 1,
namespace: 'test.test',
indexFilterSet: false,
parsedQuery: { 'stock.size':

{ '$eq': 'M' }

},
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]' ] }

}
}
},
rejectedPlans: []

This shows a SORT Stage

Generated at Thu Feb 08 08:12:23 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.