mongoldb query performance optimisation

Question

I have the following document structure in my store collection,

{
  "_id": "some_custom_id",
  "inventory": [
    {
      "productId": "some_prod_id",
      // ...restAttributes
    },
    // 500+ such items
  ]
}

I'm trying to make a query coll.find({_id:"some_id","inventory.productId":"some_prod_id"},{...})

The query sometimes takes a lot of time to return(10 secs or so). so I created an index {_id:1,"inventory.productId":1} but still no performance gain, so I tried mongo query explain and found that _id index is used and not the one I created. Then I created another index {"inventory.productId":1, _id:1}still no luck.

Here is the output of coll.find({_id:"some_id","inventory.productId":"some_prod_id"}).explain("executionStats")

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "somedb.Stores",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "_id" : {
                        "$eq" : "114"
                    }
                }, 
                {
                    "inventory.productId" : {
                        "$eq" : "41529689"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "inventory.productId" : {
                    "$eq" : "41529689"
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "_id" : 1
                },
                "indexName" : "_id_",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "_id" : []
                },
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "_id" : [ 
                        "[\"114\", \"114\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "inventory.productId" : {
                    "$eq" : "41529689"
                }
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 1,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 1,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "_id" : 1
                },
                "indexName" : "_id_",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "_id" : []
                },
                "isUnique" : true,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "_id" : [ 
                        "[\"114\", \"114\"]"
                    ]
                },
                "keysExamined" : 1,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "somecluster-shard-00-02-1jury.gcp.mongodb.net",
        "port" : 27017,
        "version" : "4.0.16",
        "gitVersion" : "2a5433168a53044cb6b4fa8083e4cfd7ba142221"
    },
    "ok" : 1.0,
    "operationTime" : Timestamp(1585112231, 1),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1585112231, 1),
        "signature" : {
            "hash" : { "$binary" : "joFIiOgu32NHAVrAO40lHKl7/i8=", "$type" : "00" },
            "keyId" : NumberLong(6778940624956555265)
        }
    }
}

so I have 2 questions,

1. How do I increase the query performance?
2. I see indexes {"inventory.productId":1, _id:1} and {_id:1,"inventory.productId":1} are having different size. what is the difference between them?

Answer 1

1. Sometimes Mongo chooses the wrong index, Basically Mongo does a mini "race" between available indexes and chooses the one that fetches 101 documents first.

   Obviously this does not necessarily mean the best index was chosen. like in your case, to avoid this you can use hint, this forces Mongo to use the index of your choice which will make the query run much faster.

2. Mongo builds its indexes as a B-trees, due to the nature of data distribution the trees are built differently and have other sizes. Theres an interesting and more in-depth explanation about how they build indexes in this video. but without diving into the source code this will remain a "black box" to you.