Lab 3.1: Explain Output isn't clear

The lab provides the following query:

exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint(REDACTED)

Then, the lab asks the given index for the hind() method, according to the following output:

{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "m201.restaurants",
    "indexFilterSet": false,
    "parsedQuery": "REDACTED",
    "winningPlan": {
      "stage": "SORT",
      "sortPattern": {
        "name": 1
      },
      "inputStage": {
        "stage": "SORT_KEY_GENERATOR",
        "inputStage": {
          "stage": "FETCH",
          "inputStage": {
            "stage": "IXSCAN",
            "keyPattern": "REDACTED",
            "indexName": "REDACTED",
            "isMultiKey": false,
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 1,
            "direction": "forward",
            "indexBounds": "REDACTED"
          }
        }
      }
    },
    "rejectedPlans": [ ]
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 3335,
    "executionTimeMillis": 20,
    "totalKeysExamined": 3335,
    "totalDocsExamined": 3335,
    "executionStages": "REDACTED"
  },
  "serverInfo": "REDACTED",
  "ok": 1
}

The explain output above shows that an in-memory sort was performed after and index scan. The answer it says is the correct one, would not produce an in memory sort since it’s sorting by the attribute name. That is, since the correct answer contains the name attribute on the end of the provided index, why would it produce an in-memory sort?

Hey @Mauricio_Junior,

Welcome to the MongoDB Community Forums! :leaves:

There was a sort stage after the index scan because range is involved for the stars field. This is the ESR rule which will be discussed later in the course, hence don’t worry if you don’t understand the SORT stage after IXSCAN stage.

The aim of this lab is to make learners familiar with reading Explain Plans by creating and executing all the indexes. I would suggest you to try creating those indexes that are there in the options, run the query given in the question and then you would see that the explain output for the index

{ "address.state": 1, "stars": 1, "name": 1 }

is very similar to the one given in the question and hence that’s the answer.

Hope this helps answer your query and that you are finding the course useful and informative too. Feel free to reach out for anything else as well.

Regards,
Satyam

1 Like

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