Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-15245

[Server] Multikey Index Page Example Issue

    XMLWordPrintableJSON

Details

    • 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.

      Attachments

        Activity

          People

            kanchana.sekhar@mongodb.com Kanchana Sekhar
            ronan.merrick@mongodb.com Ronan Merrick
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              30 weeks ago