The aggregation below actually occurs inside of a lookup but for having the explain for it I am running it alone (not in the lookup).
The problem is that it is running super slow (more than 200 seconds) because it is not using the correct index. I tried several things but it still uses wrong index.
The explain
{
"stages" : [
{
"$cursor" : {
"query" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$contact_id",
ObjectId("5fadca37b32109095e324434")
]
},
{
"$eq" : [
"$channel",
"form"
]
},
{
"$eq" : [
"$event",
"submitted"
]
},
{
"$eq" : [
"$form_id",
ObjectId("6000194aae91a5ea3fa9975b")
]
}
]
}
},
"fields" : {
"channel" : 1,
"contact_id" : 1,
"event" : 1,
"form_id" : 1,
"_id" : 0
},
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "production.events",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"$expr" : {
"$and" : [
{
"$eq" : [
"$contact_id",
{
"$const" : ObjectId("5fadca37b32109095e324434")
}
]
},
{
"$eq" : [
"$channel",
{
"$const" : "form"
}
]
},
{
"$eq" : [
"$event",
{
"$const" : "submitted"
}
]
},
{
"$eq" : [
"$form_id",
{
"$const" : ObjectId("6000194aae91a5ea3fa9975b")
}
]
}
]
}
},
{
"channel" : {
"$_internalExprEq" : "form"
}
},
{
"contact_id" : {
"$_internalExprEq" : ObjectId("5fadca37b32109095e324434")
}
},
{
"event" : {
"$_internalExprEq" : "submitted"
}
},
{
"form_id" : {
"$_internalExprEq" : ObjectId("6000194aae91a5ea3fa9975b")
}
}
]
},
"queryHash" : "F1331520",
"planCacheKey" : "0C213055",
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"$expr" : {
"$and" : [
{
"$eq" : [
"$contact_id",
{
"$const" : ObjectId("5fadca37b32109095e324434")
}
]
},
{
"$eq" : [
"$channel",
{
"$const" : "form"
}
]
},
{
"$eq" : [
"$event",
{
"$const" : "submitted"
}
]
},
{
"$eq" : [
"$form_id",
{
"$const" : ObjectId("6000194aae91a5ea3fa9975b")
}
]
}
]
}
},
{
"form_id" : {
"$_internalExprEq" : ObjectId("6000194aae91a5ea3fa9975b")
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"contact_id" : NumberLong(1),
"channel" : NumberLong(1),
"campaign_id" : NumberLong(1),
"event" : NumberLong(1)
},
"indexName" : "contact_id_1_channel_1_campaign_id_1_event_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"contact_id" : [],
"channel" : [],
"campaign_id" : [],
"event" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"contact_id" : [
"[ObjectId('5fadca37b32109095e324434'), ObjectId('5fadca37b32109095e324434')]"
],
"channel" : [
"[\"form\", \"form\"]"
],
"campaign_id" : [
"[MinKey, MaxKey]"
],
"event" : [
"[\"submitted\", \"submitted\"]"
]
}
}
},
"rejectedPlans" : [
...
],
"serverInfo" : {
"host" : "atlas-lfb6fy-shard-00-02.pdned.mongodb.net",
"port" : 27017,
"version" : "4.2.13",
"gitVersion" : "82dd40f60c55dae12426c08fd7150d79a0e28e23"
},
"ok" : 1.0,
"$clusterTime" : {
"clusterTime" : Timestamp(1620401634, 7),
"signature" : {
"hash" : { "$binary" : "1E2/PFKIKQ3NL5Gqk+kJmhjjUoA=", "$type" : "00" },
"keyId" : NumberLong(6902231878846119939)
}
},
"operationTime" : Timestamp(1620401634, 7)
}
I cutted the explain because it was too long but the fact is the index “contact_id_1_channel_1_form_id_1_event_1” is in the rejected plans.
How to use it?