Mongo - query not matching index

Question

I'm trying to set my collections indexes correctly. I've a collection that is like:

// videos collection
{
  _id: idString,
  ts: date,
  owner_id: idString,
  published: boolean,
  readyToPlay: boolean,
  private: boolean,
  deleted: boolean,
}

I'm indexing it as:

videos.ensureIndex({
  owner_id: 1,
  deleted: 1,

  published: 1,
  readyToPlay: 1,
  private: 1,

  ts: -1,
})

and I'm querying it like:

videos.find({
  owner_id: { $in: ids },
  deleted: false,
  published: true,
  private: false,
  readyToPlay: true,
})

When I'm explaining the query, I get:

[ {
  queryPlanner: { 
    plannerVersion: 1,
    namespace: 'videos',
    indexFilterSet: false,
    parsedQuery: { '$and': [
      { isPrivate: { '$eq': false } },
      { published: { '$eq': true } },
      { readyToPlay: { '$eq': true } },
      { owner_id: { '$in': [...] } },
      { deleted: { '$eq': false } },
    ] },
    winningPlan: [...],
    rejectedPlans: [],
  },
  executionStats: {
    nReturned: some docs,
    totalDocsExamined: all the docs,
    ...
  },
  serverInfo: { ... },
]

Since indexFilterSet is false, it seems that my index is not working correctly. How should I change it?

The winning plan is:

{ stage: 'FETCH',
  inputStage: {
    stage: 'SORT_MERGE',
    sortPattern: { ts: 1 },
    inputStages: [{
      stage: 'IXSCAN',
      keyPattern: {
        owner_id: 1,
        deleted: 1,
        published: 1,
        readyToPlay: 1,
        isPrivate: 1,
        ts: -1
      },
      indexName: 'owner_id_1_deleted_1_published_1_readyToPlay_1_isPrivate_1_ts_-1',
      isMultiKey: false,
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 1,
      direction: 'backward',
      indexBounds: { owner_id: [ '["id1", "id1"]' ],
        deleted: [ '[false, false]' ],
        published: [ '[true, true]' ],
        readyToPlay: [ '[true, true]' ],
        isPrivate: [ '[false, false]' ],
        ts: [ '[MinKey, MaxKey]' ]
      }
    },
    // a lot more stages, one for each key in ids
    ]
  }
}

Answer 1

Your index is being used. You should look for the winningPlan and the executionStats section. Here's what I got:

"winningPlan" : {
    "stage" : "FETCH",
    "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                    "owner_id" : 1,
                    "deleted" : 1,
                    "published" : 1,
                    "readyToPlay" : 1,
                    "private" : 1,
                    "ts" : -1
            },
            "indexName" : "owner_id_1_deleted_1_published_1_readyToPlay_1_private_1_ts_-1",
            "isMultiKey" : false,
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 1,
            "direction" : "forward",
            "indexBounds" : {
                    "owner_id" : [
                            "[\"1\", \"1\"]"
                    ],
                    "deleted" : [
                            "[false, false]"
                    ],
                    "published" : [
                            "[true, true]"
                    ],
                    "readyToPlay" : [
                            "[true, true]"
                    ],
                    "private" : [
                            "[true, true]"
                    ],
                    "ts" : [
                            "[MaxKey, MinKey]"
                    ]
            }
    }

You can see there is an index scan.