How does "nreturned" work in mongo explain() in $facet/$group to determine "examined:returned" ratio?

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.

enter image description here

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?

Looks like the alert is incorrect and you can safely ignore it - your query is as efficient as it can possibly be from what you post here.

Asya

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.