In our one of query aggregate, we are doing a lookup to a Customer collection using the CreatedByCustomerId. But unfortunately index is not getting used while doing the lookup and it’s doing a full scan of the customers list to map which is taking lot of time and failing to execute the query.
{
$match: {
IsInternal: false,
CreatedOn: {
$gte: ISODate("2025-01-01T00:00:00.000Z"),
$lte: ISODate("2025-01-16T00:00:00.000Z")
}
}
},
{
$addFields: {
Date: {
$toDate: {
$dateToString: {
date: "$CreatedOn",
timezone: "-0400"
}
}
},
LastModifiedDate: {
$toDate: {
$dateToString: {
date: "$LastModifiedOn",
timezone: "-0400"
}
}
},
CreatedById: {
$toObjectId: "$CreatedByUserId"
},
CaseOwnerId: {
$toObjectId: "$CaseOwnerUserId"
},
LastUpdatedById: {
$toObjectId: "$LastUpdatedByUserId"
}
}
},
{
$addFields: {
WeekNum: {
$week: "$Date"
}
}
},
{
$lookup: {
from: "CaseType",
localField: "CaseType",
foreignField: "_id",
as: "caseTypes"
}
},
{
$addFields: {
LastUpdatedByUserIdObjectId: {
$toObjectId: "$LastUpdatedByUserId"
}
}
},
{
**$lookup: {**
** from: "Customer",**
** localField: "CreatedByUserId",**
** foreignField: "_id",**
** as: "LastUpdatedBy"**
** }**
}
]
The below is the execution plan where it’s showing while lookup it’s not using the _id index for matching.
{
"explainVersion": "1",
"stages": [
{
"$cursor": {
"queryPlanner": {
"namespace": "storedb.Case",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{ "IsInternal": { "$eq": false } },
{
"CreatedOn": {
"$lte": "2025-01-16T00:00:00.000Z"
}
},
{
"CreatedOn": {
"$gte": "2025-01-01T00:00:00.000Z"
}
}
]
},
"queryHash": "59F73638",
"planCacheKey": "B94FE186",
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"IsInternal": 1,
"CreatedOn": -1
},
"indexName": "IsInternal_1_CreatedOn_-1",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 3,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"IsInternal": [],
"CreatedOn": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"IsInternal": ["[false, false]"],
"CreatedOn": [
"[new Date(1736985600000), new Date(1735689600000)]"
]
}
}
},
"rejectedPlans": [
{
"stage": "FETCH",
"filter": {
"IsInternal": { "$eq": false }
},
"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(1736985600000), new Date(1735689600000)]"
]
}
}
}
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 428,
"executionTimeMillis": 85825,
"totalKeysExamined": 428,
"totalDocsExamined": 428,
"executionStages": {
"stage": "FETCH",
"nReturned": 428,
"executionTimeMillisEstimate": 8,
"works": 429,
"advanced": 428,
"needTime": 0,
"needYield": 0,
"saveState": 9,
"restoreState": 9,
"isEOF": 1,
"docsExamined": 428,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 428,
"executionTimeMillisEstimate": 0,
"works": 429,
"advanced": 428,
"needTime": 0,
"needYield": 0,
"saveState": 9,
"restoreState": 9,
"isEOF": 1,
"keyPattern": {
"IsInternal": 1,
"CreatedOn": -1
},
"indexName": "IsInternal_1_CreatedOn_-1",
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 3,
"numericOrdering": false,
"alternate": "non-ignorable",
"maxVariable": "punct",
"normalization": false,
"backwards": false,
"version": "57.1"
},
"isMultiKey": false,
"multiKeyPaths": {
"IsInternal": [],
"CreatedOn": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"IsInternal": ["[false, false]"],
"CreatedOn": [
"[new Date(1736985600000), new Date(1735689600000)]"
]
},
"keysExamined": 428,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
}
},
"nReturned": 428,
"executionTimeMillisEstimate": 8
},
{
"$addFields": {
"Date": {
"$convert": {
"input": {
"$dateToString": {
"date": "$CreatedOn",
"timezone": { "$const": "-0400" }
}
},
"to": { "$const": "date" }
}
},
"LastModifiedDate": {
"$convert": {
"input": {
"$dateToString": {
"date": "$LastModifiedOn",
"timezone": { "$const": "-0400" }
}
},
"to": { "$const": "date" }
}
},
"CreatedById": {
"$convert": {
"input": "$CreatedByUserId",
"to": { "$const": "objectId" }
}
},
"CaseOwnerId": {
"$convert": {
"input": "$CaseOwnerUserId",
"to": { "$const": "objectId" }
}
},
"LastUpdatedById": {
"$convert": {
"input": "$LastUpdatedByUserId",
"to": { "$const": "objectId" }
}
}
},
"nReturned": 428,
"executionTimeMillisEstimate": 11
},
{
"$addFields": {
"WeekNum": {
"$week": { "date": "$Date" }
}
},
"nReturned": 428,
"executionTimeMillisEstimate": 11
},
{
"$lookup": {
"from": "CaseType",
"as": "caseTypes",
"localField": "CaseType",
"foreignField": "_id"
},
"totalDocsExamined": 428,
"totalKeysExamined": 428,
"collectionScans": 0,
"indexesUsed": ["_id_"],
"nReturned": 428,
"executionTimeMillisEstimate": 162
},
{
"$addFields": {
"LastUpdatedByUserIdObjectId": {
"$convert": {
"input": "$LastUpdatedByUserId",
"to": { "$const": "objectId" }
}
}
},
"nReturned": 428,
"executionTimeMillisEstimate": 162
},
**{**
** "$lookup": {**
** "from": "Customer",**
** "as": "LastUpdatedBy",**
** "localField": "CreatedByUserId",**
** "foreignField": "_id"**
** },**
** "totalDocsExamined": 110029838,**
** "totalKeysExamined": 0,**
** "collectionScans": 856,**
** "indexesUsed": [],**
** "nReturned": 428,**
** "executionTimeMillisEstimate": 85819**
** }**
],
"serverInfo": {
"host": "atlas-gj4g2l-shard-00-02.7g9o2.mongodb.net",
"port": 27017,
"version": "5.0.30",
"gitVersion": "966efda23d779a86c76c34e1b13e561d68f2bb37"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600
},
"command": {
"aggregate": "Case",
"pipeline": [
{
"$match": {
"IsInternal": false,
"CreatedOn": {
"$gte": "2025-01-01T00:00:00.000Z",
"$lte": "2025-01-16T00:00:00.000Z"
}
}
},
{
"$addFields": {
"Date": {
"$toDate": {
"$dateToString": {
"date": "$CreatedOn",
"timezone": "-0400"
}
}
},
"LastModifiedDate": {
"$toDate": {
"$dateToString": {
"date": "$LastModifiedOn",
"timezone": "-0400"
}
}
},
"CreatedById": {
"$toObjectId": "$CreatedByUserId"
},
"CaseOwnerId": {
"$toObjectId": "$CaseOwnerUserId"
},
"LastUpdatedById": {
"$toObjectId": "$LastUpdatedByUserId"
}
}
},
{
"$addFields": {
"WeekNum": { "$week": "$Date" }
}
},
{
"$lookup": {
"from": "CaseType",
"localField": "CaseType",
"foreignField": "_id",
"as": "caseTypes"
}
},
{
"$addFields": {
"LastUpdatedByUserIdObjectId": {
"$toObjectId": "$LastUpdatedByUserId"
}
}
},
{
"$lookup": {
"from": "Customer",
"localField": "CreatedByUserId",
"foreignField": "_id",
"as": "LastUpdatedBy"
}
}
],
"cursor": {},
"maxTimeMS": 60000,
"$db": "storedb"
},
"ok": 1,
"$clusterTime": {
"clusterTime": {
"$timestamp": "7464851334534004740"
},
"signature": {
"hash": "6vyWA1OpaQYwyQ/KND2bDe5s5yU=",
"keyId": {
"low": 2,
"high": 1724960866,
"unsigned": false
}
}
},
"operationTime": {
"$timestamp": "7464851334534004740"
}
}
We have verified that the local field and the foreign fields are both string (Please find the screen shot below of both local and customers _id fileds data types).
Please let us know if there is something we are missing out here (why index _id is not getting applied on lookup to customers)
One observation: When we try to convert the local field from string to Object Id use for mapping in lookup it’s applying the index but it’s not matching any data even though data is there in customers collection.