Index not getting applied while $lookup (Customers lookup in my query, _id index is not getting applied) for one of my query but for other query it’s getting applied.
Below is the plan details which is not taking index on customers lookup:
{
“explainVersion”: “1”,
“stages”: [
{
“$cursor”: {
“queryPlanner”: {
“namespace”: “storedb.Case”,
“indexFilterSet”: false,
“parsedQuery”: {
“$and”: [
{
“CreatedOn”: {
“$lte”: “2024-03-11T00:00:00.000Z”
}
},
{
“CreatedOn”: {
“$gte”: “2024-01-01T00:00:00.000Z”
}
}
]
},
“queryHash”: “DA7A911E”,
“planCacheKey”: “10BEEF52”,
“maxIndexedOrSolutionsReached”: false,
“maxIndexedAndSolutionsReached”: false,
“maxScansToExplodeReached”: false,
“winningPlan”: {
“stage”: “PROJECTION_SIMPLE”,
“transformBy”: {
“id": true,
“IsInternal”: true,
“CaseNumber”: true,
“Subject”: true,
“CaseComments”: true
},
“inputStage”: {
“stage”: “FETCH”,
“inputStage”: {
“stage”: “IXSCAN”,
“keyPattern”: {
“CreatedOn”: -1
},
“indexName”: "CreatedOn-1”,
“isMultiKey”: false,
“multiKeyPaths”: {
“CreatedOn”:
},
“isUnique”: false,
“isSparse”: false,
“isPartial”: false,
“indexVersion”: 2,
“direction”: “forward”,
“indexBounds”: {
“CreatedOn”: [
“[new Date(1710115200000), new Date(1704067200000)]”
]
}
}
}
},
“rejectedPlans”:
},
“executionStats”: {
“executionSuccess”: true,
“nReturned”: 1510,
“executionTimeMillis”: 222975,
“totalKeysExamined”: 1510,
“totalDocsExamined”: 1510,
“executionStages”: {
“stage”: “PROJECTION_SIMPLE”,
“nReturned”: 1510,
“executionTimeMillisEstimate”: 1,
“works”: 1511,
“advanced”: 1510,
“needTime”: 0,
“needYield”: 0,
“saveState”: 5,
“restoreState”: 5,
“isEOF”: 1,
“transformBy”: {
“id": true,
“IsInternal”: true,
“CaseNumber”: true,
“Subject”: true,
“CaseComments”: true
},
“inputStage”: {
“stage”: “FETCH”,
“nReturned”: 1510,
“executionTimeMillisEstimate”: 1,
“works”: 1511,
“advanced”: 1510,
“needTime”: 0,
“needYield”: 0,
“saveState”: 5,
“restoreState”: 5,
“isEOF”: 1,
“docsExamined”: 1510,
“alreadyHasObj”: 0,
“inputStage”: {
“stage”: “IXSCAN”,
“nReturned”: 1510,
“executionTimeMillisEstimate”: 1,
“works”: 1511,
“advanced”: 1510,
“needTime”: 0,
“needYield”: 0,
“saveState”: 5,
“restoreState”: 5,
“isEOF”: 1,
“keyPattern”: {
“CreatedOn”: -1
},
“indexName”: "CreatedOn-1”,
“isMultiKey”: false,
“multiKeyPaths”: {
“CreatedOn”:
},
“isUnique”: false,
“isSparse”: false,
“isPartial”: false,
“indexVersion”: 2,
“direction”: “forward”,
“indexBounds”: {
“CreatedOn”: [
“[new Date(1710115200000), new Date(1704067200000)]”
]
},
“keysExamined”: 1510,
“seeks”: 1,
“dupsTested”: 0,
“dupsDropped”: 0
}
}
},
“allPlansExecution”:
}
},
“nReturned”: 1510,
“executionTimeMillisEstimate”: 1
},
{
“$unwind”: {
“path”: “$CaseComments”
},
“nReturned”: 1891,
“executionTimeMillisEstimate”: 6
},
{
“$addFields”: {
“CreatedById”: “$CaseComments.CreatedById”,
“CommentCreatedDate”: “$CaseComments.CreatedOn”
},
“nReturned”: 1891,
“executionTimeMillisEstimate”: 7
},
{
“$lookup”: {
“from”: “Customer”,
“as”: “Customers”,
“localField”: “CreatedById”,
“foreignField”: “_id”
},
“totalDocsExamined”: 438532718,
“totalKeysExamined”: 0,
“collectionScans”: 3782,
“indexesUsed”: ,
“nReturned”: 1891,
“executionTimeMillisEstimate”: 222956
},
{
“$addFields”: {
“CommentedBy”: {
“$arrayElemAt”: [
“$Customers.Email”,
{
“$const”: 0
}
]
}
},
“nReturned”: 1891,
“executionTimeMillisEstimate”: 222963
},
{
“$project”: {
“_id”: true,
“CommentCreatedDate”: true,
“Case Number”: “$CaseNumber”,
“Case Subject”: “$Subject”,
“CaseComments”: {
“Description”: true
},
“CommentedBy::multi-filter”: “$CommentedBy”,
“Case Type”: “$IsInternal”
},
“nReturned”: 1891,
“executionTimeMillisEstimate”: 222970
}
],
“serverInfo”: {
“host”: “atlas-gj4g2l-shard-00-01.7g9o2.mongodb.net”,
“port”: 27017,
“version”: “5.0.26”,
“gitVersion”: “0b4f1ea980b5380a66425a90b414106a191365f4”
},
“serverParameters”: {
“internalQueryFacetBufferSizeBytes”: 104857600,
“internalQueryFacetMaxOutputDocSizeBytes”: 104857600,
“internalLookupStageIntermediateDocumentMaxSizeBytes”: 104857600,
“internalDocumentSourceGroupMaxMemoryBytes”: 104857600,
“internalQueryMaxBlockingSortMemoryUsageBytes”: 104857600,
“internalQueryProhibitBlockingMergeOnMongoS”: 0,
“internalQueryMaxAddToSetBytes”: 104857600,
“internalDocumentSourceSetWindowFieldsMaxMemoryBytes”: 104857600
},
“command”: {
“aggregate”: “Case”,
“pipeline”: [
{
“$match”: {
“CreatedOn”: {
“$gte”: “2024-01-01T00:00:00.000Z”,
“$lte”: “2024-03-11T00:00:00.000Z”
}
}
},
{
“$project”: {
“CaseNumber”: 1,
“Subject”: 1,
“IsInternal”: 1,
“CaseComments”: 1
}
},
{
“$unwind”: “$CaseComments”
},
{
“$addFields”: {
“CreatedById”: “$CaseComments.CreatedById”,
“CommentCreatedDate”: “$CaseComments.CreatedOn”
}
},
{
“$lookup”: {
“from”: “Customer”,
“localField”: “CreatedById”,
“foreignField”: “_id”,
“as”: “Customers”
}
},
{
“$addFields”: {
“CommentedBy”: {
“$arrayElemAt”: [
“$Customers.Email”,
0
]
}
}
},
{
“$project”: {
“Case Number”: “$CaseNumber”,
“Case Subject”: “$Subject”,
“CaseComments.Description”: 1,
“CommentedBy::multi-filter”: “$CommentedBy”,
“CommentCreatedDate”: 1,
“Case Type”: “$IsInternal”
}
}
],
“cursor”: {},
“maxTimeMS”: 60000,
“$db”: “storedb”
},
“ok”: 1,
“$clusterTime”: {
“clusterTime”: {
“$timestamp”: “7354702921088368645”
},
“signature”: {
“hash”: “mJzHl346uQo2bICb2Iw2z5f1xJM=”,
“keyId”: {
“low”: 1,
“high”: 1702919047,
“unsigned”: false
}
}
},
“operationTime”: {
“$timestamp”: “7354702921088368645”
}
}
In other query, it’s using the index:
{
“$lookup”: {
“from”: “Customer”,
“as”: “Customers”,
“localField”: “SalesRepId”,
“foreignField”: “_id”
},
“totalDocsExamined”: 224,
“totalKeysExamined”: 224,
“collectionScans”: 0,
“indexesUsed”: [
“id”
],
“nReturned”: 224,
“executionTimeMillisEstimate”: 73
},
Not able to find out the mistake i am doing. Please let me know if need more details