title: MongoDB aggregate, slow query even with indexed sort parameter
body: <p>I've got the following query:</p>
  
  <pre><code>db.Visitor.aggregate([
    {"$sort":{"updatedAt":-1}},
    {"$match":{
      "$and":[
        {"events.platformId":"8"},
        {"events.applicationId":{"$in":["354","325","373","177","379","417","415","416"]}},
        {"events.date":{"$gte":ISODate("2018-01-26 15:23:00")}},
        {"events.date":{"$lte":ISODate("2018-01-26 23:59:59")}}
      ]
    }},
    {"$project":{
      "events":{
        "$filter":{
          "input":"$events",
          "as":"event",
          "cond":{
            "$and":[
              {"$eq":["$$event.platformId","8"]},
              {"$in":["$$event.applicationId",["354","325","373","177","379","417","415","416"]]},
              {"$gte":["$$event.date",ISODate("2018-01-26 15:23:00")]},
              {"$lte":["$$event.date",ISODate("2018-01-26 23:59:59")]}
            ]
          }
        }
      }
    }},
    {"$limit":15},
  ], {"maxTimeMS": 2500}).pretty()
  </code></pre>
  
  <p>And it fails as it reaches the max execution time. If I take the sort away, it returns in terms of milliseconds.</p>
  
  <p>I thought it could be an index issue, but it already exists:</p>
  
  <pre><code>    {
              "v" : 1,
              "key" : {
                      "updatedAt" : -1
              },
              "name" : "expiration_ttl",
              "ns" : "analytics.Visitor",
              "expireAfterSeconds" : NumberLong(1296000)
      }
  </code></pre>
  
  <p>Do you have any clue?</p>
  
  <p>Thanks :)</p>
  
  <p>EDIT:
  After a weekend of thoughts, I came up with the idea to create a step in the pipeline between <code>$sort</code> and <code>$match</code>, creating an additional <code>$match</code> filtering the documents in the collection having <code>updatedAt</code> in the range specified in <code>events.date</code> or <code>requests.date</code> (the former <code>$match</code>). In this way the query won't take lots of time, being the resulting collection much more smaller than the original one, letting the match work even without having indexes on every match condition's attributes.</p>