will this $sort and $limit query use the index or be a blocking in memory sort?

Question

index:

{
  last_logged_in: -1
}

query:

user.aggregate([
  {
    $match: {
      age: { $gt: 18 },
      city: { $in: ["chicago", "paris"] }
    }
  },
  {
    $sort: {
      last_logged_in: -1
    }
  },
  {
    $limit: 10000
  }
])

It is matching based on fields that are not part of the index, but the $sort is by the indexed field.

Will this query require an in memory sort?

Answer 1

Explain output will show you that the index can be used:

{
  stage: 'LIMIT',
  limitAmount: 10000,
  inputStage: {
    stage: 'FETCH',
    filter: {
      '$and': [
        { age: { '$gt': 18 } },
        { city: { '$in': [ 'chicago', 'paris' ] } }
      ]
    },
    inputStage: {
      stage: 'IXSCAN',
      keyPattern: { last_logged_in: -1 },
      indexName: 'last_logged_in_-1',
      isMultiKey: false,
      multiKeyPaths: { last_logged_in: [] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { last_logged_in: [ '[MaxKey, MinKey]' ] }
    }
  }
}

The lack of a dedicated SORT stage is the indication that the index is providing the sort. That said, you'll notice that the scan is not bounded (see the indexBounds). So the effectiveness of this index for the query entirely depends on the selectivity of the predicates in the $match. If it is highly unselective then the index will be useful. The more selective it becomes, the less effective the index will be.

You could instead consider following the ESR guidance. In your situation this would mean creating the following index:

{ city: 1, last_logged_in: -1, age: 1 }

I would expect this index to perform better for this query than the single field one that you've referenced.