Mongodb not using index for array of objects

Question

I am trying to fetch few documents in a collection, by making a find query on array of nested objects. Nested objects are indexed but find query is not using the index to fetch documents.

Here is the structure of a document.

    "_id" : ObjectId("5bc6498c1ec4062983c4f4ef"),
    "appId" : ObjectId("5bbc775036021bea06d9bbc2"),

    "status" : "active",


    "segmentations" : [ 
       {
            "name" : "ch-1",
            "values" : [ 
                'true'
            ],
            "type" : "string"
        },
        {
            "name" : "browerInfo",
            "values" : [ 
                "Firefox"
            ],
            "version" : [ 
                "62.0"
            ],
            "majorVersion" : [ 
                "62"
            ],
            "type" : "string"
        }, 
        {
            "name" : "OS",
            "values" : [ 
                "Ubuntu"
            ],
            "type" : "string"
        }, 
        {
            "name" : "lastVisitTime",
            "values" : [ 
                1539721615231.0
            ],
            "type" : "number"
        }
    ]
}

Here are the index fields.

    {
        "v" : 2,
        "key" : {
            "appId" : 1,
            "status" : 1,
            "segmentations.name" : 1,
            "segmentations.values" : 1
        },
        "name" : "SEGMENT_INDEX",
        "ns" : "test.Collname"
    }

below is the find find query i was executing

db.Collname.find({
    appId: ObjectId("5c6a8ef544ff62c73bdb98fc"),
    "segmentations.name": 'ch-1',
    'segmentations.values': 'true',


    status: 'active'
}, {})

I tried to get the query execution information using

<above query>.explain("executionStats") 

The result is

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.Collname",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "appId" : {
                        "$eq" : ObjectId("5c6a8ef544ff62c73bdb98fc")
                    }
                }, 
                {
                    "segmentations.name" : {
                        "$eq" : "ch-1"
                    }
                }, 
                {
                    "segmentations.values" : {
                        "$eq" : "true"
                    }
                }, 
                {
                    "status" : {
                        "$eq" : "active"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "segmentations.values" : {
                    "$eq" : "true"
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "appId" : 1.0,
                    "status" : 1.0,
                    "segmentations.name" : 1.0,
                    "segmentations.values" : 1.0
                },
                "indexName" : "SEGMENT_INDEX",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "appId" : [],
                    "status" : [],
                    "segmentations.name" : [ 
                        "segmentations"
                    ],
                    "segmentations.values" : [ 
                        "segmentations", 
                        "segmentations.values"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "appId" : [ 
                        "[ObjectId('5c6a8ef544ff62c73bdb98fc'), ObjectId('5c6a8ef544ff62c73bdb98fc')]"
                    ],
                    "status" : [ 
                        "[\"active\", \"active\"]"
                    ],
                    "segmentations.name" : [ 
                        "[\"ch-1\", \"ch-1\"]"
                    ],
                    "segmentations.values" : [ 
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 28176,
        "executionTimeMillis" : 72,
        "totalKeysExamined" : 28176,
        "totalDocsExamined" : 28176,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "segmentations.values" : {
                    "$eq" : "true"
                }
            },
            "nReturned" : 28176,
            "executionTimeMillisEstimate" : 70,
            "works" : 28177,
            "advanced" : 28176,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 220,
            "restoreState" : 220,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 28176,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 28176,
                "executionTimeMillisEstimate" : 10,
                "works" : 28177,
                "advanced" : 28176,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 220,
                "restoreState" : 220,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "appId" : 1.0,
                    "status" : 1.0,
                    "segmentations.name" : 1.0,
                    "segmentations.values" : 1.0
                },
                "indexName" : "SEGMENT_INDEX",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "appId" : [],
                    "status" : [],
                    "segmentations.name" : [ 
                        "segmentations"
                    ],
                    "segmentations.values" : [ 
                        "segmentations", 
                        "segmentations.values"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "appId" : [ 
                        "[ObjectId('5c6a8ef544ff62c73bdb98fc'), ObjectId('5c6a8ef544ff62c73bdb98fc')]"
                    ],
                    "status" : [ 
                        "[\"active\", \"active\"]"
                    ],
                    "segmentations.name" : [ 
                        "[\"ch-1\", \"ch-1\"]"
                    ],
                    "segmentations.values" : [ 
                        "[MinKey, MaxKey]"
                    ]
                },
                "keysExamined" : 28176,
                "seeks" : 1,
                "dupsTested" : 28176,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "sys3029",
        "port" : 27017,
        "version" : "4.0.9",
        "gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765"
    },
    "ok" : 1.0
}

I could see from executionStats that "segmentations.values" field is not used in "IXSCAN" stage. And there is an extra filter stage on "segmentations.values". IXSCAN stage took just 10ms, where as FILTER stage took 50ms.

I couldn't understand why the field is not included in IXSCAN stage. My collection has around 3.2 Million documents and because of this issue query execution time is very high than expected.

Please help me fix the issue. Thank you in advance.

Please suggest me If I need to change my database structure, If it is not possible in mongodb,you can suggest some other database which supports above operations.

Answer 1

The following query will use your index for both of your array fields:

.find({
    appId: ObjectId("5c6a8ef544ff62c73bdb98fc"),
    segmentations:{$elemMatch:{name: 'ch-1',values: 'true'}},
    status: 'active'
}, {})

If you are not using $elemMatch, MongoDB can compound the bounds for the array item keys with either the bounds for "segmentations.name" or the bounds for "segmentations.values", but not both. In order to compound the bounds for "segmentations.name" with the bounds for "segmentations.values", the query must use $elemMatch.

To compound together the bounds for index keys from the same array:

• the index keys must share the same field path up to but excluding the field names,
• and the query must specify predicates on the fields using $elemMatch on that path.

I suggest you to read mongodb docs about multikey-index-bounds and also about $elemMatch.