mongodb example: why is this not a covered query?-CodePudding

i created the following collection:

students> db.students.find()
[
  { _id: 1, grades: [ 95, 92, 90 ], average: 145 },
  { _id: 2, grades: [ 98, 100, 102 ], average: 145 },
  { _id: 3, grades: [ 95, 110, 100 ], average: 145 }
]

these are the available indexes:

students> db.students.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { grades: 1 }, name: 'grades_1' },
  { v: 2, key: { average: 1 }, name: 'average_1' }
]

If i run this query, i expect it to be covered, since it needs just the 'average' field, which is an indexed field.

db.students.find({average:145}, {_id:0, grades:0})

Instead, i get a the following output using explain("executionStats"):

[...]
winningPlan: {
      stage: 'PROJECTION_DEFAULT',
      transformBy: { _id: 0, grades: 0 },
      inputStage: {
        stage: 'FETCH',
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: { average: 1 },
          indexName: 'average_1',
          isMultiKey: false,
          multiKeyPaths: { average: [] },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: { average: [ '[145, 145]' ] }
        }
      }
    }

And this:

 totalDocsExamined: 3

Which means that the query had to read the documents in the collection... what am i missing? Thanks

CodePudding user response：

You need to specify projection to extract only indexed fields:

db.students.find({average:145}, {_id:0, average:1})

Mongodb is schemaless so there is no way to know what fields are in the document until FETCH phase.

One of the conditions for covered query to happen (from https://docs.mongodb.com/manual/core/query-optimization/#covered-query) is

all the fields returned in the results are in the same index.

The {_id:0, grades:0} spells "I want all fields but grades", and {_id:0, average:1} means "I want only average".