I have a query with following stages -
match
project
facet -> {
resA -[ group by A],
resB -[ group by B],
resC -[ group by C]
}
The index i have used is {uid: 1}
which is basically filtering out in $match
stage with 8308 records below. I have to group on 8308 records in $facet
with different fields in each result set(resA, resB…).
When i do explain i get following results -
"winningPlan": {
"stage": "PROJECTION_SIMPLE",
"transformBy": {
??
},
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": { "uid": 1 },
"indexName": "uid_1",
"isMultiKey": false,
"multiKeyPaths": { "uid": [] },
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"uid": [
"[\"cdc67cf2-0c23-4d32-b103-f78503824b18\", \"cdc67cf2-0c23-4d32-b103-f78503824b18\"]"
]
}
}
}
},
"rejectedPlans": [
??
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 8308,
"executionTimeMillis": 1397,
"totalKeysExamined": 8308,
"totalDocsExamined": 8308,
"executionStages": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 8308,
"executionTimeMillisEstimate": 82,
"works": 8309,
"advanced": 8308,
"needTime": 0,
"needYield": 0,
"saveState": 10,
"restoreState": 10,
"isEOF": 1,
"transformBy": {
??
},
"inputStage": {
"stage": "FETCH",
"nReturned": 8308,
"executionTimeMillisEstimate": 15,
"works": 8309,
"advanced": 8308,
"needTime": 0,
"needYield": 0,
"saveState": 10,
"restoreState": 10,
"isEOF": 1,
"docsExamined": 8308,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 8308,
"executionTimeMillisEstimate": 4,
"works": 8309,
"advanced": 8308,
"needTime": 0,
"needYield": 0,
"saveState": 10,
"restoreState": 10,
"isEOF": 1,
"keyPattern": { "uid": 1 },
"indexName": "uid_1",
"isMultiKey": false,
"multiKeyPaths": { "uid": [] },
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"uid": [
"[\"cdc67cf2-0c23-4d32-b103-f78503824b18\", \"cdc67cf2-0c23-4d32-b103-f78503824b18\"]"
]
},
"keysExamined": 8308,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
},
"allPlansExecution": [
{
"nReturned": 101,
"executionTimeMillisEstimate": 0,
"totalKeysExamined": 101,
"totalDocsExamined": 101,
"executionStages": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 101,
"executionTimeMillisEstimate": 0,
"works": 101,
"advanced": 101,
"needTime": 0,
"needYield": 0,
"saveState": 1,
"restoreState": 0,
"isEOF": 0,
"transformBy": {
??
},
"inputStage": {
"stage": "FETCH",
"nReturned": 101,
"executionTimeMillisEstimate": 0,
"works": 101,
"advanced": 101,
"needTime": 0,
"needYield": 0,
"saveState": 1,
"restoreState": 0,
"isEOF": 0,
"docsExamined": 101,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 101,
"executionTimeMillisEstimate": 0,
"works": 101,
"advanced": 101,
"needTime": 0,
"needYield": 0,
"saveState": 1,
"restoreState": 0,
"isEOF": 0,
"keyPattern": { "uid": 1 },
"indexName": "uid_1",
"isMultiKey": false,
"multiKeyPaths": { "uid": [] },
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"uid": [
"[\"cdc67cf2-0c23-4d32-b103-f78503824b18\", \"cdc67cf2-0c23-4d32-b103-f78503824b18\"]"
]
},
"keysExamined": 101,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
}
} ,
]
}
},
"nReturned": 8308,
"executionTimeMillisEstimate": 94
},
{
"$facet": {
"resA": [
{
"$teeConsumer": {},
"nReturned": 8308,
"executionTimeMillisEstimate": 1229
},
{
"$match": { "resA": { "$not": { "$eq": null } } },
"nReturned": 8308,
"executionTimeMillisEstimate": 1248
},
{
"$group": {
"_id": "$resA",
"count": { "$sum": { "$const": 1 } }
},
"nReturned": 374,
"executionTimeMillisEstimate": 1250
},
{
"$sort": { "sortKey": { "count": -1 } },
"nReturned": 374,
"executionTimeMillisEstimate": 1250
},
{
"$project": {
"_id": true,
"label": "$_id",
"count": "$count",
"percent": {
"$round": [
{
"$ifNull": [
{
"$multiply": [
{ "$divide": ["$count", { "$const": 8308 }] },
{ "$const": 100 }
]
},
{ "$const": 0 }
]
}
]
}
},
"nReturned": 374,
"executionTimeMillisEstimate": 1250
}
],
.
.
.
.
},
"nReturned": 1,
"executionTimeMillisEstimate": 1388
}
My Question is how nreturned gets evaluated and i have gone through the Docs . But its not clear to me that which nreturned gets considered at the end. Suppose in the above executionStats
the nreturned : 8308
but the one at the last below $facet
shows nreturned : 1
. So to determine the examined:returned
ratio. Which param is being considered. I have checked my mongo atlas profiler stats. It shows nreturned : 1
, that makes the examined:returned
ratio to 8308.
Is this because of $facet
stage? Because i’d need processed results in facet stage, as i have mutiple grouping separate in facet
as resA, resB … If the examined:returned
is 8308. Is this problematic? My query needs to group on the 8308 records with multiple fields in each facet stage. Also, the atlas throws an alert Query Targeting: Scanned Objects / Returned has gone above 1000
. Is this the cause?