Covered query is fetching doc from collection instead of index

About the Community Welcome
, ,
#1

There are billions of documents in this collection.
How can I structure this indexes and query so that it become covered query and fast response.

totalDocsExamined is 101 instead of zero.

This is my collection.
there will be one more field ‘b’ which will be set while updating. and it will be unique. value of field ‘a’ will be unique.

db.coll1.findOne()
{
  _id: ObjectId("641c5a5c441e7d1a23e51f0c"),
  a: 'a',
  active: true,
}

This is the query.

db.coll1.explain('executionStats').findAndModify({query: {active: false, _id: {$gte: ObjectId("641c5a5c441e7d1a53e51f0c")}}, update: {b: 'b', active: true}, fields: { _id: 1, a: 1, b: 1 }, new: true })

executionStats: {
    executionSuccess: true,
    nReturned: 1,
    executionTimeMillis: 2,
    totalKeysExamined: 101,
    totalDocsExamined: 101,
    executionStages: {
      stage: 'UPDATE',
      nReturned: 1,
      executionTimeMillisEstimate: 0,
      works: 2,
      advanced: 1,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      nMatched: 1,
      nWouldModify: 1,
      nWouldUpsert: 0,
      inputStage: {
        stage: 'FETCH',
        nReturned: 101,
        executionTimeMillisEstimate: 0,
        works: 101,
        advanced: 101,
        needTime: 0,
        needYield: 0,
        saveState: 1,
        restoreState: 1,
        isEOF: 0,
        docsExamined: 101,
        alreadyHasObj: 0,
        inputStage: {
          stage: 'IXSCAN',
          nReturned: 101,
          executionTimeMillisEstimate: 0,
          works: 101,
          advanced: 101,
          needTime: 0,
          needYield: 0,
          saveState: 1,
          restoreState: 1,
          isEOF: 0,
          keyPattern: { active: 1, _id: 1, b: 1, a: 1 },
          indexName: 'active_1__id_1_b_1_a_1',
          isMultiKey: false,
          multiKeyPaths: { active: [], _id: [], b: [], a: [] },
          isUnique: false,
          isSparse: false,
          isPartial: true,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: {
            active: [ '[false, false]' ],
            _id: [
              "[ObjectId('641c5a5c441e7d1a53e51f0c'), ObjectId('ffffffffffffffffffffffff')]"
            ],
            b: [ '[MinKey, MaxKey]' ],
            a: [ '[MinKey, MaxKey]' ]
          },
          keysExamined: 101,
          seeks: 1,
          dupsTested: 0,
          dupsDropped: 0
        }
      }
    }
  },

This. is indexes.

db.coupons.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    key: { active: 1, _id: 1, b: 1, a: 1 },
    name: 'active_1__id_1_b_1_a_1',
    partialFilterExpression: { active: false }
  },
]
#2

I am not too sure but an update cannot really be covered. The query/projection is covered but since the whole document needs to be written for the update it has to be fetched.

As for your partial index, there is absolutely no point, to have active:1 in your keys, it will always be false. It is actually wasteful since it will always be false.