I'm currently working with DocumentDB 3.6 and have two collections in a database:
users - This collection contains a field called name.
users_position - This collection uses userId as a foreign key, which has a unique index.
I executed the following query to fetch a user with a specific _id and its corresponding position:
db.getCollection('users').explain("executionStats").aggregate([
{
$match: {
_id: 123
},
},
{
$lookup: {
from: "users_position",
localField: "_id",
foreignField: "userId",
as: "userPosition",
},
}
]);
The result indicated a collection scan (COLLSCAN) rather than an index scan (IXSCAN) on the foreign table, which was unexpected. Here are the execution stats:
"executionStats": {
"executionSuccess": true,
"executionTimeMillis": "913.307",
"planningTimeMillis": "1.651",
"executionStages": {
"stage": "SUBSCAN",
"nReturned": "1",
"executionTimeMillisEstimate": "911.598",
"inputStage": {
"stage": "HASH_AGGREGATE",
"nReturned": "1",
"executionTimeMillisEstimate": "911.529",
"inputStage": {
"stage": "HASH_LOOKUP",
"nReturned": "1",
"executionTimeMillisEstimate": "911.476",
"inputStages": [
{
"stage": "COLLSCAN",
"nReturned": "482385",
"executionTimeMillisEstimate": "746.718"
},
{
"stage": "HASH",
"nReturned": "1",
"executionTimeMillisEstimate": "0.128",
"inputStage": {
"stage": "IXSCAN",
"nReturned": "1",
"executionTimeMillisEstimate": "0.036",
"indexName": "_id_",
"direction": "forward"
}
}
]
}
}
}
}
I'm confused about why the foreign table utilized a collection scan instead of an index scan.
Hi. Can you post the indexes and stats for the "users_position" collection?