A query passes 100,000 documents to a $lookup
stage, which joins based on an indexed field. Here is the explain:
{
"$lookup": {
"from": "user",
"as": "user",
"localField": "_id",
"foreignField": "_id",
"let": {},
"pipeline": [
{
"$project": {
"_id": 1
}
}
]
},
"totalDocsExamined": 0,
"totalKeysExamined": 100008,
"collectionScans": 0,
"indexesUsed": [
"_id_"
],
"nReturned": 100008,
"executionTimeMillisEstimate": 18801
}
It took 19 seconds. Without the $lookup
the query takes less than 2 seconds.
This means that the time complexity is not O(Log(N))
But it should be as searching by index is O(Log(N)) complexity.
The local collection is user
and the foreign collection is also user
. I am joining by the same collection, so the data lives in the exact same location.
What are the mechanics of $lookup
that creates so much overhead?