title: Using mongodb $lookup on big documents is slow
body: <p>I have users_users document with 966.628 entries and orders_orders with 1.419.081 (and above 14.000.000 entries inside orders).</p>
  
  <p>I need to retrieve user's data based on multiple filters (location, birthday, nb orders, nb products bought, etc.) but it never ends. I'm new with mongodb so I probably do bad things and need to learn.</p>
  
  <pre><code>db.users_users.aggregate([{
      $match: {
          locale: {
              $in: ["fr_FR", "fr_BE"]
          },
          "users_addresses.country_iso2": "FR",
          mobile: {
              $ne: null
          }
      }
  }, {
      $lookup: {
          from: "orders_orders",
          localField: "_id",
          foreignField: "id_user",
          as: "orders"
      }
  }, {
      $unwind: "$orders"
  }, {
      $group: {
          "_id": "$_id",
          "lastname": {
              $first: "$lastname"
          },
          "firstname": {
              $first: "$firstname"
          },
          "email": {
              $first: "$email"
          },
          "date_birth": {
              $first: "$date_birth"
          },
          "locale": {
              $first: "$locale"
          },
          "nb_orders": {
              $sum: 1
          },
          "order_total": {
              $sum: "$orders.tax_inclusive_amount"
          },
          "last_order": {
              $max: "$orders.date_creation"
          },
          "entries": {
              $push: "$orders.entries"
          },
          "countries": {
              $addToSet: "$users_addresses.id_country"
          },
      }
  }, {
      $unwind: "$entries"
  }, {
      $unwind: "$entries"
  }, {
      $group: {
          "_id": "$_id",
          "lastname": {
              $first: "$lastname"
          },
          "firstname": {
              $first: "$firstname"
          },
          "email": {
              $first: "$email"
          },
          "date_birth": {
              $first: "$date_birth"
          },
          "locale": {
              $first: "$locale"
          },
          "nb_orders": {
              $first: "$nb_orders"
          },
          "order_total": {
              $first: "$order_total"
          },
          "last_order": {
              $first: "$last_order"
          },
          "countries": {
              $first: "$countries"
          },
          "nb_entries": {
              $sum: 1
          }
      }
  }, {
      $match: {
          nb_orders: {
              $gt: 1
          },
          nb_entries: {
              $gt: 10
          }
      }
  }])
  </code></pre>
  
  <p><strong>EDIT:</strong> 
  Indexes, documents and output as requested</p>
  
  <p><strong>users_users indexes</strong></p>
  
  <pre><code>&gt; db.users_users.getIndexes()
  [
      {
          "v" : 1,
          "key" : {
              "_id" : 1
          },
          "name" : "_id_",
          "ns" : "elf.users_users"
      },
      {
          "v" : 1,
          "key" : {
              "pre_mongified_id" : 1
          },
          "name" : "pre_mongified_id_1",
          "ns" : "elf.users_users"
      },
      {
          "v" : 1,
          "key" : {
              "email" : 1
          },
          "name" : "email_1",
          "ns" : "elf.users_users",
          "background" : true
      },
      {
          "v" : 1,
          "key" : {
              "date_birth" : 1
          },
          "name" : "date_birth_1",
          "ns" : "elf.users_users"
      },
      {
          "v" : 1,
          "key" : {
              "mobile" : 1
          },
          "name" : "mobile_1",
          "ns" : "elf.users_users"
      },
      {
          "v" : 1,
          "key" : {
              "locale" : 1
          },
          "name" : "locale_1",
          "ns" : "elf.users_users"
      },
      {
          "v" : 1,
          "key" : {
              "users_addresses.postal_code" : 1
          },
          "name" : "users_addresses.postal_code_1",
          "ns" : "elf.users_users"
      },
      {
          "v" : 1,
          "key" : {
              "users_addresses.city" : 1
          },
          "name" : "users_addresses.city_1",
          "ns" : "elf.users_users"
      },
      {
          "v" : 1,
          "key" : {
              "users_addresses.country_iso2" : 1
          },
          "name" : "users_addresses.country_iso2_1",
          "ns" : "elf.users_users"
      }
  ]
  </code></pre>
  
  <p><strong>orders_orders indexes</strong></p>
  
  <pre><code>&gt; db.orders_orders.getIndexes()
  [
      {
          "v" : 1,
          "key" : {
              "_id" : 1
          },
          "name" : "_id_",
          "ns" : "elf.orders_orders"
      },
      {
          "v" : 1,
          "key" : {
              "pre_mongified_id" : 1
          },
          "name" : "pre_mongified_id_1",
          "ns" : "elf.orders_orders"
      },
      {
          "v" : 1,
          "key" : {
              "id_user" : 1
          },
          "name" : "id_user_1",
          "ns" : "elf.orders_orders"
      },
      {
          "v" : 1,
          "key" : {
              "entries.id_target" : 1,
              "entries.type" : 1
          },
          "name" : "entries.id_target_1_entries.type_1",
          "ns" : "elf.orders_orders",
          "background" : true
      },
      {
          "v" : 1,
          "key" : {
              "number" : 1
          },
          "name" : "number_1",
          "ns" : "elf.orders_orders"
      }
  ]
  </code></pre>
  
  <p><strong>users_users sample</strong></p>
  
  <pre><code>&gt; db.users_users.find().limit(2).pretty()
  {
      "_id" : ObjectId("56c46f6eae6f960fb6f59107"),
      "id_civilitytitle" : 2,
      "date_creation" : ISODate("2008-09-05T18:17:42Z"),
      "date_update" : null,
      "firstname" : "xxx",
      "lastname" : "YYY",
      "email" : "xxx@xxx.fr",
      "phone" : "xxxxxxxxxx",
      "mobile" : null,
      "fax" : "",
      "disabled" : false,
      "confirmed" : true,
      "date_birth" : null,
      "locale" : "fr_FR",
      "users_addresses" : [
          {
              "id_country" : ObjectId("56c43401ae6f960fb6000396"),
              "name" : "Adresse",
              "fullname" : "YYY xxx",
              "address1" : "xxx",
              "address2" : null,
              "city" : "xxx",
              "postal_code" : "11610",
              "country_iso2" : "FR"
          }
      ]
  }
  {
      "_id" : ObjectId("56c46f6eae6f960fb6f59108"),
      "id_civilitytitle" : 2,
      "date_creation" : ISODate("2008-09-06T14:38:59Z"),
      "date_update" : null,
      "firstname" : "aaa",
      "lastname" : zzz",
      "email" : "xxx@xxx.fr",
      "phone" : "xx xx xx xx xx",
      "mobile" : null,
      "fax" : "",
      "disabled" : false,
      "confirmed" : true,
      "date_birth" : null,
      "locale" : "fr_FR",
      "users_addresses" : [
          {
              "id_country" : ObjectId("56c43401ae6f960fb6000396"),
              "name" : "Adresse",
              "fullname" : "aaa zzz",
              "address1" : "xxx",
              "address2" : null,
              "city" : "xxx",
              "postal_code" : "59180",
              "country_iso2" : "FR"
          }
      ]
  }
  </code></pre>
  
  <p><strong>orders_orders sample</strong></p>
  
  <pre><code>&gt; db.orders_orders.find().skip(5).limit(2).pretty()
  {
      "_id" : ObjectId("56c46ccfae6f960fb6dfe9c3"),
      "id_user" : ObjectId("56c46f6eae6f960fb6f59109"),
      "date_creation" : ISODate("2008-09-09T08:21:56Z"),
      "number" : "c000026",
      "tax_inclusive_amount" : 10,
      "shipping_fees" : 5.95,
      "paid" : null,
      "cancelled" : "cancelled",
      "locale" : null,
      "from_mobile" : false,
      "entries" : [
          {
              "_id" : ObjectId("56c4340dae6f960fb60008b5"),
              "id_order" : ObjectId("56c46ccfae6f960fb6dfe9c3"),
              "id_target" : 58,
              "type" : "reference",
              "quantity" : 1,
              "reference" : "#4203",
              "name" : "XXX",
              "tax_inclusive_price_unit" : 1,
              "tax_inclusive_price_total" : 1,
              "tax_rates" : "a:1:{i:0;O:38:\"Catalog_Model_References_Container_Tax\":5:{s:7:\"\u0000*\u0000rate\";d:0.196000000000000007549516567451064474880695343017578125;s:7:\"\u0000*\u0000name\";s:6:\"19.60%\";s:7:\"\u0000*\u0000type\";s:32:\"cbf1c9560e4d3dbae5d65339aefed7b0\";s:13:\"\u0000*\u0000proportion\";d:1;s:8:\"\u0000*\u0000value\";N;}}",
              "weight" : null
          },
          {
              "_id" : ObjectId("56c4340dae6f960fb60008be"),
              "id_order" : ObjectId("56c46ccfae6f960fb6dfe9c3"),
              "id_target" : 247,
              "type" : "reference",
              "quantity" : 1,
              "reference" : "#1711",
              "name" : "XXX",
              "tax_inclusive_price_unit" : 1,
              "tax_inclusive_price_total" : 1,
              "tax_rates" : "a:1:{i:0;O:38:\"Catalog_Model_References_Container_Tax\":5:{s:7:\"\u0000*\u0000rate\";d:0.196000000000000007549516567451064474880695343017578125;s:7:\"\u0000*\u0000name\";s:6:\"19.60%\";s:7:\"\u0000*\u0000type\";s:32:\"cbf1c9560e4d3dbae5d65339aefed7b0\";s:13:\"\u0000*\u0000proportion\";d:1;s:8:\"\u0000*\u0000value\";N;}}",
              "weight" : null
          }
      ]
  }
  {
      "_id" : ObjectId("56c46ccfae6f960fb6dfe9c4"),
      "id_user" : ObjectId("56c46f6eae6f960fb6f5911d"),
      "date_creation" : ISODate("2008-09-09T12:32:40Z"),
      "number" : "c000027",
      "tax_inclusive_amount" : 15,
      "shipping_fees" : 5.95,
      "paid" : "paid",
      "cancelled" : null,
      "locale" : null,
      "from_mobile" : false,
      "entries" : [
          {
              "_id" : ObjectId("56c4340dae6f960fb60008bf"),
              "id_order" : ObjectId("56c46ccfae6f960fb6dfe9c4"),
              "id_target" : 105,
              "type" : "reference",
              "quantity" : 1,
              "reference" : "#9011",
              "name" : "XXX",
              "tax_inclusive_price_unit" : 1,
              "tax_inclusive_price_total" : 1,
              "tax_rates" : "a:1:{i:0;O:38:\"Catalog_Model_References_Container_Tax\":5:{s:7:\"\u0000*\u0000rate\";d:0.196000000000000007549516567451064474880695343017578125;s:7:\"\u0000*\u0000name\";s:6:\"19.60%\";s:7:\"\u0000*\u0000type\";s:32:\"cbf1c9560e4d3dbae5d65339aefed7b0\";s:13:\"\u0000*\u0000proportion\";d:1;s:8:\"\u0000*\u0000value\";N;}}",
              "weight" : null
          },
          {
              "_id" : ObjectId("56c435b0ae6f960fb614c240"),
              "id_order" : ObjectId("56c46ccfae6f960fb6dfe9c4"),
              "id_target" : 364,
              "type" : "reference",
              "quantity" : 1,
              "reference" : "#1710",
              "name" : "xxx",
              "tax_inclusive_price_unit" : 1,
              "tax_inclusive_price_total" : 1,
              "tax_rates" : "a:1:{i:0;O:38:\"Catalog_Model_References_Container_Tax\":5:{s:7:\"\u0000*\u0000rate\";d:0.196000000000000007549516567451064474880695343017578125;s:7:\"\u0000*\u0000name\";s:6:\"19.60%\";s:7:\"\u0000*\u0000type\";s:32:\"cbf1c9560e4d3dbae5d65339aefed7b0\";s:13:\"\u0000*\u0000proportion\";d:1;s:8:\"\u0000*\u0000value\";N;}}",
              "weight" : null
          }
      ]
  }
  </code></pre>
  
  <p><strong>expected output</strong></p>
  
  <p>Multiple data from users_users for each lines (firstname, lastname, email, birth_date, locale, ...)</p>