Mongo Db not using proper index

I have a below query  aggregate query :
The query planner not using index as expected.
Below are the indexes available:
As per the query  it should use index with name : MDMN-134199 but 
its picking some other index because of which we get high cpu utilization.
will be great if i get some pointers
Index: ------------------->

type or paste code here

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_"
    },
    {
        "v" : 2,
        "key" : {
            "_key.primary" : "hashed"
        },
        "name" : "_v3_shard_key",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "_key.primary" : 1,
            "_meta.type" : 1,
            "_key.changeList" : 1
        },
        "name" : "_v3_key",
        "background" : true,
        "unique" : true
    },
    {
        "v" : 2,
        "key" : {
            "_meta.type" : 1,
            "_meta.status" : 1,
            "_meta.states.base" : 1,
            "_key.changeList" : 1,
            "_meta.changeList" : 1
        },
        "name" : "_v3_key_meta_cl_states",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "_meta.type" : 1,
            "_meta.states.base" : 1,
            "_meta.states.searchIndex" : 1,
            "_meta.jobId" : 1,
            "_key.primary" : 1
        },
        "name" : "_v3_type_states_search",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "_meta.type" : 1,
            "_members.sourceSystem" : 1,
            "_members.sourcePKey" : 1,
            "_parent" : 1
        },
        "name" : "_metatype_members_parent",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "_meta.type" : 1,
            "_meta.businessId" : 1
        },
        "name" : "_metatypebusinessid",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "_meta.type" : 1,
            "_meta.status" : 1,
            "_businessIds" : 1
        },
        "name" : "_metatype_businessids_index",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "_meta.type" : 1,
            "_meta.status" : 1,
            "_meta.states.base" : 1,
            "_meta.changeList" : 1,
            "_meta.lastUpdateDate" : 1
        },
        "name" : "_v3_type_pointintime",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "_meta.states.base" : 1,
            "_meta.status" : 1,
            "_meta.type" : 1,
            "_key.primary" : 1,
            "_meta.lastUpdateDate" : 1
        },
        "name" : "MDMN-134199"
    }
]

Query : -------------->
[
  {
    $match: {
      $and: [
        {
          "_key.primary": {
            $gte: "617f5be211e7793ce371d615",
            $lt: "61802b3b7c2d4b70a73dd33d",
          },
        },
        {
          $and: [
            {
              "_meta.lastUpdateDate": { $gt: 0 },
            },
            {
              "_meta.lastUpdateDate": {
                $lt: 1708610064534,
              },
            },
            { "_meta.status": "ACTIVE" },
            { "_meta.type": "c360.person" },
          ],
        },
        {
          "_meta.states.base": {
            $in: ["ACTIVE"],
          },
        },
      ],
    },
  },
]
1 Like

Could you provide explain of the query and which version of the database you are using