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