Slow MongoDB lookup aggregation on very few and very small documents
<p>There are many questions on SO about slow MongoDB aggregation for either or both of those cases:</p>

<ul>
<li>a huge number of documents to be retrieved</li>
<li>very big documents</li>
</ul>

<p>What bothers me today is that, in a prototype I'm setting up, I have a consistently <strong>very slow</strong> query where:</p>

<ul>
<li>no more than 10 documents are to be retrieved</li>
<li>each document does not exceed perhaps 300 bytes of information</li>
</ul>

<h2>With lookup aggregation</h2>

<p>This query performs a <code>lookup</code> aggregation over the main documents to be retrieved, and is written like this (using Python's MongoEngine)</p>

<pre><code>author_lookup = {
    '$lookup': {
        'from': 'users',
        'localField': 'author_id',
        'foreignField': '_id',
        'as': 'author'
     }
}

cursor = post_collection.Post.objects.all().aggregate(author_lookup)
</code></pre>

<p>At the risk of repeating myself, as of this prototype's current state, there are no more than 10 <code>Post</code> documents to be retrieved, and perhaps 3 <code>Author</code> documents to be looked up.</p>

<p>Here are the timed results on 5 consecutive hits on this query:</p>

<pre><code>1.9418830871582031 seconds
3.0417070388793945 seconds
2.1725950241088867 seconds
1.3020501136779785 seconds
3.728671073913574 seconds
</code></pre>

<p>Pretty slow, mm?</p>

<h2>Without lookup aggregation</h2>

<p>Now, if I remove the aggregation part and just query this:</p>

<pre><code>cursor = post_collection.Post.objects.all()
</code></pre>

<p>All else being equal, here are the timed results for 5 consecutives hits on this new query:</p>

<pre><code>0.26596999168395996 seconds
0.00011920928955078125 seconds
0.00011205673217773438 seconds
0.0001659393310546875 seconds
0.00013589859008789062 seconds
</code></pre>

<h2>How to interpret those results?</h2>

<p>First thing I can note, is that when using aggregation, the first result is not slower than the following results. This makes me think that with the aggregated query in its current form, no cache operation is performed.</p>

<p>-> <strong>Is there a way to cache results when using the aggregation framework with <code>$lookup</code>?</strong></p>

<p>Second thing is, aside from the obvious caching mechanism at work when <strong>not</strong> using aggregation, it seems that the first query alone (not cached) is executed ten times faster than when using aggregation. I find it hard to believe that performing a <code>lookup</code> aggregate over 4 <code>Author</code> documents on a total of 10 <code>Post</code> documents take 10 times the speed of retrieving only the 10 <code>Post</code> documents.</p>

<p>All of this leads me to think that, despite of having followed MongoDB documentation on lookup aggregations, I <strong>must</strong> be doing something wrong here.</p>