title: MongoDB $lookup + $sort with larger datasets
body: <p>I followed <a href="https://docs.mongodb.com/v5.0/reference/operator/aggregation/lookup/#examples" rel="nofollow noreferrer">https://docs.mongodb.com/v5.0/reference/operator/aggregation/lookup/#examples</a> to create a lookup aggregation between 2 collections. (MongoDB 4.4)
  However, when I try to add <code>$sort</code>, for larger datasets like 100K documents, the query times out and no results are returned. If the dataset is small, like 50 documents, it’s fine.</p>
  <p>Any suggestions? I followed ESR (Equality &gt; Sort &gt; Range) rule and setup the following:</p>
  <pre><code>Collection 1:  source_products
   - source_id
   - gid
   - title
   
  index({source_id: 1, gid: 1}, {unique: true})
  index({source_id: 1, title: 1})
  
  Collection 2:  destination_products
   - target_gid
  
  </code></pre>
  <p>Here’s the aggregation, the explain plan shows that <code>source_id_1_gid_1</code> is being used, so it seems that the sort on <code>title</code> isn’t using the correct index.</p>
  <pre><code>db.source_products.aggregate(
            [
              {
                '$lookup' =&gt; {
                  from: 'destination_products',
                  localField: 'gid',
                  foreignField: 'target_gid',
                  as: 'destination_product'
                }
              },
              {
                '$sort' =&gt; {
                  title: 1
                }
              }
            ]
          )
  </code></pre>
  <p>I also tried these indices and they aren’t being used either:</p>
  <pre><code>index({source_id: 1, gid: 1, title: 1})
  </code></pre>
  <pre><code>index({source_id: 1, title: 1})
  </code></pre>