title: MongoDB query slow on very large collection - retrieve distinct documents based on field
body: <p>I am having some problems making my MongoDB queries more efficient. My documents have the following format:</p>
  
  <pre><code>{
    _id: UUID,
    p: 'some.path',
    t: 'Fri Dec 12 2014 09:26:18 GMT+0100 (CET)',
    v: 123.4
  }
  </code></pre>
  
  <p>where path and timestamp are indexed. The path is an identifier in our system, where we have around 15-16000 different paths. Our logging application is logging these values at around 3000 documents each second, and this is running fine without problems. </p>
  
  <p>My problem is when retrieving a "snapshot in time" of these values; I want to make a query to know what all the signals are, at a given time. That means for each path, get the latest value where the timestamp is less or equal to the requested timestamp, and return all of these documents.</p>
  
  <p>So in SQL/pseudocode:</p>
  
  <pre><code>results = []
  for each distinct path:
    find document where p == path and t &lt;= timestamp, order by t desc, limit 1
      results.push(result)
  </code></pre>
  
  <p>I have also gotten the same results by using aggregate with match, sort and group:</p>
  
  <pre><code>aggregate(
  [
      { $match: { t : { $lte: timestamp}} },
      { $sort: { t : -1 } },
      { $group:
      {
      _id: { x: "$p"},
      p: { $last: "$p"},
      t: { $last: "$t"},
      v: { $last: "$v"}
      }
      }
  ]
  </code></pre>
  
  <p>However, with my collection currently clocking in at around 250 million records, both of these methods are very slow. I am really stuck on this, and can't seem to get this to work. If I cannot find a solution to this problem I will probably have to start looking at other databases that might support these types of queries better.</p>