title: MongoDB - aggregation performance tuning
body: <p>One of my aggregation pipelines is running rather slow. </p>
  
  <h2>About the collection</h2>
  
  <p>The collection is named as <code>Document</code> and each document can belong to multiple campaigns and be in one of the five statues, 'a' to 'e'. A small portion of documents may belong to no documents and its <code>campaigns</code> field is set to <code>null</code>. </p>
  
  <p>Sample document:</p>
  
  <pre><code>{_id:id,  campaigns:['c1', 'c2], status:'a', ...other fields...}
  </code></pre>
  
  <p>Some collection stats</p>
  
  <ul>
  <li>Number of documents: 2 million only :( </li>
  <li>Size: 2GB</li>
  <li>Average document size: 980 bytes. </li>
  <li>Storage Size: 780MB </li>
  <li>Total index size: 134MB </li>
  <li>Number of indexes: 12 </li>
  <li>Number of fields in document: 30-40, may have array or objects. </li>
  </ul>
  
  <h2>About the Query</h2>
  
  <p>The query is targeting to count the number of documents per campaign per status if its status is in ['a', 'b', 'c']</p>
  
  <pre><code>[
      {$match:{campaigns:{$ne:null}, status:{$in:['a','b','c']}}},
      {$unwind:'$campaigns'},
      {$group:{_id:{campaign:'$campaigns', status:'$status'}, total:{$sum:1}}}
  ]
  </code></pre>
  
  <p>It's expected that the aggregation is going to hit almost the whole collection. 
  When without index the aggregation is taking around <code>8 seconds</code> to complete. </p>
  
  <p>I tried to create an index on</p>
  
  <pre><code>{campaings:1, status:1}
  </code></pre>
  
  <p>Explaining plan shows that the index was scanned but the aggregation took <code>near 11 seconds</code> to complete. </p>
  
  <h2>Question</h2>
  
  <p>The index consists all fields required by the aggregation to do the counting. Should the aggregation not hit the index only? The index is only 10MB in size. How could it be slower? If not index, any other recommendation to tune the query? </p>
  
  <p>Winning plan shows:</p>
  
  <pre><code>{
      "stage" : "FETCH",
      "filter" : {"$not" : {"campaigns" : {"$eq" : null}}},
      "inputStage" : {
          "stage" : "IXSCAN",
          "keyPattern" : {"campaigns" : 1.0,"status" : 1.0},
          "indexName" : "campaigns_1_status_1",
          "isMultiKey" : true,
          "isUnique" : false,
          "isSparse" : false,
          "isPartial" : false,
          "indexVersion" : 1,
          "direction" : "forward",
          "indexBounds" : {
              "campaigns" : ["[MinKey, null)", "(null, MaxKey]"],
              "status" : [ "[\"a\", \"a\"]", "[\"b\", \"b\"]", "[\"c\", \"c\"]"]
          }
      }
  }
  </code></pre>
  
  <p>If no index, winning plan: </p>
  
  <pre><code>{
      "stage" : "COLLSCAN",
      "filter" : {
          "$and":[
              {"status": {"$in": ["a", "b", "c"]}},
              {"$not" : {"campaigns": {"$eq" : null}}}
          ]
      },
      direction" : "forward"
  }
  </code></pre>
  
  <h2>Update</h2>
  
  <p>As requested by @Kevin, here're some details about other all indexes, size in MB. </p>
  
  <pre><code>"indexSizes" : {
      "_id_" : 32,
      "team_1" : 8, //Single value field of ObjectId
      "created_time_1" : 16, //Document publish time in source system.
      "parent_1" : 2, //_id of parent document. 
      "by.id_1" : 13, //_id of author from a different collection. 
      "feedids_1" : 8, //Array, _id of ETL jobs contributing to sync of this doc.
      "init_-1" : 2, //Initial load time of the doc.
      "campaigns_1" : 10, //Array, _id of campaigns
      "last_fetch_-1" : 13, //Last sync time of the doc. 
      "categories_1" : 8, //Array, _id of document categories. 
      "status_1" : 8, //Status
      "campaigns_1_status_1" : 10 //Combined index of campaign _id and status. 
  },
  </code></pre>