title: MongoDB aggregate framework very slow when have group
body: <p>I am trying to make an aggregate query with <code>"group"</code> to get the total of results.</p>
  
  <p>The total of <code>"requested_items"</code> (my results) are +- 1.900.000.</p>
  
  <p>If I execute with "group", the query is very slow (+- 300sec).</p>
  
  <p>If I execute without "group", the query is very fast (+- 1sec).</p>
  
  <p>What am I doing wrong? </p>
  
  <p>The sample code is below.
  <br />
  <br />
  <strong>SLOW QUERY</strong></p>
  
  <pre><code>db.minute.aggregate([
      { $match: {
          $and: [
              { "status": "Homologado" },
              { "requested_items.status": /aceito/i },
          ]
      } },
      { $sort: {'_id': 1}},
      { $unwind: "$requested_items" },
      { $unwind: "$requested_items.winner" },
      { $match: {
          $and: [
              { "status": "Homologado" },
              { "requested_items.status": /aceito/i },
          ]
      } },
      { $project: {
          "_id": 1
      } },
      { $group: {
          "_id" : null,
          "total" : {$sum: 1},
      } },
  ], {allowDiskUse: true});
  </code></pre>
  
  <p><br /></p>
  
  <p><strong>FAST QUERY</strong></p>
  
  <pre><code>db.minute.aggregate([
      { $match: {
          $and: [
              { "status": "Homologado" },
              { "requested_items.status": /aceito/i },
          ]
      } },
      { $sort: {'_id': 1}},
      { $unwind: "$requested_items" },
      { $unwind: "$requested_items.winner" },
      { $match: {
          $and: [
              { "status": "Homologado" },
              { "requested_items.status": /aceito/i },
          ]
      } },
      { $project: {
          "_id": 1
      } },
  ], {allowDiskUse: true});
  </code></pre>
  
  <p><br />
  <strong>DB STRUCTURE</strong></p>
  
  <pre><code>{
      "_id" : "12345678ABCD",
      "field_1" : [ 
          {
              "a" : null,
              "b" : "ABC"
          }, 
          {
              "code" : null,
              "b" : "ABCD"
          }
      ],
      "status" : "Homologado",
      "initial_date" : ISODate("2016-05-24T11:31:00.000Z"),
      "field_2" : [ 
          {
              "a" : "ABC",
              "b" : "ABCDE"
          }, 
          {
              "a" : "ABCF",
              "b" : "ABCDEF"
          }
      ],
      "field_3" : "Lorem ipsum dolor sit amet...",
      "field_4" : [ 
          {
              "date" : ISODate("2016-05-24T13:54:48.000Z"),
              "a" : "Text",
              "b" : "More text..."
          }
      ],
      "field_4" : 12312321,
      "field_5" : ISODate("2016-05-24T13:55:00.000Z"),
      "field_6" : "ABCD",
      "requested_items" : [ 
          {
              "status" : " Aceito e Habilitado",
              "field_a" : "Text...",
              "winner" : [ 
                  {
                      "a" : "23213.213213.23/232-23",
                      "b" : 130446,
                      "c" : 543223,
                      "d" : NumberLong(2),
                      "e" : "ABC 123 FULANO",
                      "f" : "text",
                      "g" : {
                          "description" : "TEXT TEXT TEXT"
                      }
                  },
                  {
                      "a" : "23213.213213.23/232-23",
                      "b" : 130446,
                      "c" : 543223,
                      "d" : NumberLong(2),
                      "e" : "ABC 123 FULANO",
                      "f" : "text",
                      "g" : {
                          "description" : "TEXT TEXT TEXT"
                      }
                  }
              ],
              "field_c" : {
                  "_id" : ObjectId("5744dd3271af88052f0cc343"),
                  "a" : "TEXT",
                  "b" : "TEXT"
              },
              "field_d" : NumberLong(2),
              "field_e" : 5223,
              "field_f" : "Não",
              "field_g" : "-",
              "field_h" : {
                  "field_a1" : [ 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }, 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }
                  ],
                  "field_a2" : [ 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }, 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }
                  ],
                  "field_a3" : {},
                  "field_a4" : [ 
                      {
                          "date" : ISODate("2016-05-24T11:34:32.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:12:54.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:48:21.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:55:38.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:55:47.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T13:01:36.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T13:15:02.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }
                  ]
              },
              "field_i" : "Não",
              "field_j" : 1
          }, 
          {
              "status" : " Aceito e Habilitado",
              "field_a" : "Text...",
              "winner" : [ 
                  {
                      "a" : "23213.213213.23/232-23",
                      "b" : 130446,
                      "c" : 543223,
                      "d" : NumberLong(2),
                      "e" : "ABC 123 FULANO",
                      "f" : "text",
                      "g" : {
                          "description" : "TEXT TEXT TEXT"
                      }
                  }
              ],
              "field_c" : {
                  "_id" : ObjectId("5744dd3271af88052f0cc343"),
                  "a" : "TEXT",
                  "b" : "TEXT"
              },
              "field_d" : NumberLong(2),
              "field_e" : 5223,
              "field_f" : "Não",
              "field_g" : "-",
              "field_h" : {
                  "field_a1" : [ 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }, 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }
                  ],
                  "field_a2" : [ 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }, 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }
                  ],
                  "field_a3" : {},
                  "field_a4" : [ 
                      {
                          "date" : ISODate("2016-05-24T11:34:32.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:12:54.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:48:21.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:55:38.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:55:47.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T13:01:36.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T13:15:02.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }
                  ]
              },
              "field_i" : "Não",
              "field_j" : 2
          }, 
          {
              "status" : " Aceito e Habilitado",
              "field_a" : "Text...",
              "winner" : [ 
                  {
                      "a" : "23213.213213.23/232-23",
                      "b" : 130446,
                      "c" : 543223,
                      "d" : NumberLong(2),
                      "e" : "ABC 123 FULANO",
                      "f" : "text",
                      "g" : {
                          "description" : "TEXT TEXT TEXT"
                      }
                  }
              ],
              "field_c" : {
                  "_id" : ObjectId("5744dd3271af88052f0cc343"),
                  "a" : "TEXT",
                  "b" : "TEXT"
              },
              "field_d" : NumberLong(2),
              "field_e" : 5223,
              "field_f" : "Não",
              "field_g" : "-",
              "field_h" : {
                  "field_a1" : [ 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }, 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }
                  ],
                  "field_a2" : [ 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }, 
                      {
                          "a" : "23213.213213.23/232-23",
                          "b" : ISODate("2016-05-23T23:54:21.000Z"),
                          "c" : 103432446,
                          "d" : 522343,
                          "e" : "Sim",
                          "f" : NumberLong(2),
                          "g" : "TEXT TEXT TEXT",
                          "h" : "Sim",
                          "i" : {
                              "a" : "TEXT TEXT TEXT"
                          }
                      }
                  ],
                  "field_a3" : {},
                  "field_a4" : [ 
                      {
                          "date" : ISODate("2016-05-24T11:34:32.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:12:54.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:48:21.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:55:38.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T12:55:47.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T13:01:36.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }, 
                      {
                          "date" : ISODate("2016-05-24T13:15:02.000Z"),
                          "A" : "TEXT",
                          "B" : "TEXT"
                      }
                  ]
              },
              "field_i" : "Não",
              "field_j" : 3
          }, 
      ],
      "field_7" : "TEXT",
      "field_8" : {
          "a" : "TEXT",
          "b" : "TEXT",
          "c" : "324234",
          "d" : "TEXT TEXT TEXT TEXT"
      },
      "field_9" : 43234
  }
  </code></pre>
  
  <p><br />
  <strong>EXPLAIN</strong></p>
  
  <pre><code>{
      "waitedMS" : NumberLong(0),
      "stages" : [ 
          {
              "$cursor" : {
                  "query" : {
                      "$and" : [ 
                          {
                              "status" : "Homologado"
                          }, 
                          {
                              "requested_items.status" : /aceito/i
                          }
                      ]
                  },
                  "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "module_database.minute",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                          "$and" : [ 
                              {
                                  "status" : {
                                      "$eq" : "Homologado"
                                  }
                              }, 
                              {
                                  "requested_items.status" : /aceito/i
                              }
                          ]
                      },
                      "winningPlan" : {
                          "stage" : "COLLSCAN",
                          "filter" : {
                              "$and" : [ 
                                  {
                                      "status" : {
                                          "$eq" : "Homologado"
                                      }
                                  }, 
                                  {
                                      "requested_items.status" : /aceito/i
                                  }
                              ]
                          },
                          "direction" : "forward"
                      },
                      "rejectedPlans" : []
                  }
              }
          }, 
          {
              "$unwind" : {
                  "path" : "$requested_items"
              }
          }, 
          {
              "$unwind" : {
                  "path" : "$requested_items.winner"
              }
          }, 
          {
              "$match" : {
                  "$and" : [ 
                      {
                          "status" : "Homologado"
                      }, 
                      {
                          "requested_items.status" : /aceito/i
                      }
                  ]
              }
          }, 
          {
              "$group" : {
                  "_id" : {
                      "$const" : null
                  },
                  "numberOfdocs" : {
                      "$sum" : {
                          "$const" : 1
                      }
                  }
              }
          }
      ],
      "ok" : 1
  }
  </code></pre>
  
  <p>My server is:<br />
  OS: UBUNTU14/64<br />
  CPU: 6<br />
  RAM: 16 GB<br />
  Total Storage: 80 GB<br />
  Running just the tests of my question.</p>