title: Performance tuning aggregation query?
body: <p>I am attempting to aggregate some information for a collection I have using the query below:</p>
  
  <pre><code>db.myCollection.aggregate([
      {$match: {survey: ObjectId("54570ff2968d6e492b0e4db7")}},
      {$unwind: "$answers"},
      {$unwind: "$answers.values"},
      {$project: {q: "$answers.question", l: "$answers.values.label"}},
      {$group:{
              _id: "$l",
              total: {$sum: 1},
              question: {$first: "$q"}
          }
      }]
      ,
      {
          allowDiskUse: true,
      }
  );
  </code></pre>
  
  <p>Sample data can be seen <a href="http://pastebin.com/0MNbW8Q5" rel="nofollow">here</a>.  </p>
  
  <p>When I aggregate relatively small amounts of data the performance is fine.  When I aggregate larger amounts of data (20,000 documents) i'm seeing runtimes of about 3.5 seconds, and when I run it against very large amounts of data (1,000,000 documents) I see runtimes of 1-2 minutes. </p>
  
  <p>I have several indexes on <code>myCollection</code> but I think the only one that applies to the above query is <code>{survey: 1, created: 1}</code></p>
  
  <p>When I include the explain for the above query it shows <code>BTreeCursor</code> under <code>stages.$cursor.plan.cursor</code> which I believe means the query is using necessary indexes (<a href="http://pastebin.com/gxX2D92a" rel="nofollow">full explain available here</a>).</p>
  
  <p>I read through <a href="http://docs.mongodb.org/manual/core/aggregation-pipeline-optimization/" rel="nofollow">MongoDB's optimization guidelines</a> and it seems like I'm already using the applicable ones.</p>
  
  <p>Can anyone suggest anything to help improve the performance of the above query considering what I have already tried?  Is there anything obvious that I am missing in my attempts to speed this guy up?</p>
  
  <p>UPDATE - including sample data:</p>
  
  <pre><code>{
     email: "test@test.com,
    answers:[
      {  
         question: ObjectId("54571089968d6e492b10133d"),
         values: [{label: "option 1", value: true}]
      }
    ]
  },
  {
    email: "another@test.com,
     answers:[
       {  
          question: ObjectId("54571089968d6e492b10133d"),
          values: [{label: "option 1", value: true}]
       }
     ]
  },
  ,
  {
     email: "one@more.com,
    answers:[
      {  
         question: ObjectId("54571089968d6e492b10133d"),
         values: [
                   {label: "option 2", value: true},
                   {label: "option 1", value: true}
                 ]
      }
    ]
  }
  </code></pre>
  
  <p>UPDATE 2 - including the explain:</p>
  
  <pre><code>{
      "stages" : [
          {
              "$cursor" : {
                  "query" : {
                      "survey" : ObjectId("54570ff2968d6e492b0e4db7")
                  },
                  "fields" : {
                      "answers" : 1,
                      "_id" : 1
                  },
                  "plan" : {
                      "cursor" : "BtreeCursor ",
                      "isMultiKey" : false,
                      "scanAndOrder" : false,
                      "indexBounds" : {
                          "survey" : [
                              [
                                  ObjectId("54570ff2968d6e492b0e4db7"),
                                  ObjectId("54570ff2968d6e492b0e4db7")
                              ]
                          ],
                          "created" : [
                              [
                                  {
                                      "$minElement" : 1
                                  },
                                  {
                                      "$maxElement" : 1
                                  }
                              ]
                          ]
                      },
                      "allPlans" : [
                          {
                              "cursor" : "BtreeCursor ",
                              "isMultiKey" : false,
                              "scanAndOrder" : false,
                              "indexBounds" : {
                                  "survey" : [
                                      [
                                          ObjectId("54570ff2968d6e492b0e4db7"),
                                          ObjectId("54570ff2968d6e492b0e4db7")
                                      ]
                                  ],
                                  "created" : [
                                      [
                                          {
                                              "$minElement" : 1
                                          },
                                          {
                                              "$maxElement" : 1
                                          }
                                      ]
                                  ]
                              }
                          }
                      ]
                  }
              }
          },
          {
              "$project" : {
                  "answers" : true
              }
          },
          {
              "$unwind" : "$answers"
          },
          {
              "$unwind" : "$answers.values"
          },
          {
              "$project" : {
                  "q" : "$answers.question",
                  "l" : "$answers.values.label"
              }
          },
          {
              "$group" : {
                  "_id" : "$l",
                  "total" : {
                      "$sum" : {
                          "$const" : 1
                      }
                  },
                  "question" : {
                      "$first" : "$q"
                  }
              }
          }
      ],
      "ok" : 1
  }
  </code></pre>