title: Why sort is affecting match to use indexes on MongoDB 3.0?
body: <p>I want to share a problem/fix I had on MongoDB 3.0 :</p>
  
  <p>I was using MongoDB 2.4 and had the following query performed on the DB:</p>
  
  <p>Assumptions:</p>
  
  <ul>
  <li>Too many records on database : more than 25000000</li>
  <li>all keys (key1, key2, ..., key6) are strings, except _id and key5 which are numbers</li>
  <li>there is an compound index on {key1 : 1, key2 : 1, key3 : 1}</li>
  </ul>
  
  <p>Query 1:</p>
  
  <pre><code>db.getCollection('myCollection').aggregate([
  { $match: { key1: "Value_for_Key1", key2: "Value_for_Key2", key3: "Value_for_Key3" , key4: { $exists: false }, key5 : 0 } },
  { $sort: { _id: 1 } },
  { $group: { _id: { key1: "$key1", key2: "$key2", key3: "$key3", key5: "$key5" }, latestValue: { $last: "$key6" }, id: { $last: "$_id" } } },
  { $project: { latestValue: "$latestValue", key1: "$_id.key1", key2: "$_id.key2", key3: "$_id.key3", key5: "$_id.key5" } }
  ])
  </code></pre>
  
  <p>To put in words : I wanted to fetch the more recently created "key6" value (based on incremental _id), using a group formed with (key1, key2, key3, key5)</p>
  
  <p>My first perfomance issue was around the $match stage, which wasn't using my {key1, key2, key3} index. So, for fixing that, I splitted into 2 stages and worked perfectly:</p>
  
  <pre><code>db.getCollection('myCollection').aggregate([
  { $match: { key1: "Value_for_Key1", key2: "Value_for_Key2", key3: "Value_for_Key3" } },
  { $match: { key4: { $exists: false }, key5 : 0 } },
  { $sort: { _id: 1 } },
  { $group: { _id: { key1: "$key1", key2: "$key2", key3: "$key3", key5: "$key5" }, latestValue: { $last: "$key6" }, id: { $last: "$_id" } } },
  { $project: { latestValue: "$latestValue", key1: "$_id.key1", key2: "$_id.key2", key3: "$_id.key3", key5: "$_id.key5" } }
  ])
  </code></pre>
  
  <p>But now I migrated to MongoDB 3.0 and the query above had again performance issues.</p>
  
  <p>On this MongoDB version they implemented an optimization (<a href="https://docs.mongodb.com/v3.0/core/aggregation-pipeline-optimization/#match-match-coalescence" rel="nofollow">here</a>) which combine my $match's into a single one, but for some reason, it wasn't using my index as it was before.</p>
  
  <p>When I added a dummy $project stage (I call it dummy because I'm just projecting all keys) between my $match and $sort, it fixes the issue: </p>
  
  <pre><code>db.getCollection('myCollection').aggregate([
  { $match: { key1: "Value_for_Key1", key2: "Value_for_Key2", key3: "Value_for_Key3", key4: { $exists: false }, key5 : 0 } },
  { $project: { _id : "$_id", key1: "$key1", key2: "$key2", key3: "$key3", key5: "$key5", key6: "$key6" } },
  { $sort: { _id: 1 } },
  { $group: { _id: { key1: "$key1", key2: "$key2", key3: "$key3", key5: "$key5" }, latestValue: { $last: "$key6" }, id: { $last: "$_id" } } },
  { $project: { latestValue: "$latestValue", key1: "$_id.key1", key2: "$_id.key2", key3: "$_id.key3", key5: "$_id.key5" } }
  ])
  </code></pre>
  
  <p>So, I didn't find any documentation on MongoDB saying a $sort after a $match may impact on indexes usage. Does anyone know anything about it?</p>