title: Indexing not utilized during the MongoDB aggregation query
body: <p>I have stuck somewhere in MongoDB aggregate query. I tried to generate a summary report from the database which contains 110M records. during the report generation, I faced the following issues
  1).Even though the collection is indexed they are not utilized for the search.
  2).Once query execution finished memory of DB server not decreased.
  3)query take considerable time to return the result.</p>
  <p>im useing mongodb Atlas v4.2.8
  sample document</p>
  <pre><code>{
      &quot;_id&quot;: {
          &quot;$oid&quot;: &quot;5eb122f714d0510011e3a184&quot;
      },
      &quot;from&quot;: &quot;Star_friends&quot;,
      &quot;to&quot;: &quot;94713414047&quot;,
      &quot;accountName&quot;: &quot;ZM&quot;,
      &quot;accountId&quot;: &quot;ZM&quot;,
      &quot;campaignName&quot;: &quot;test 1&quot;,
      &quot;campaignId&quot;: &quot;5eb122f1e921c3001922f73c&quot;,
      &quot;campaignType&quot;: &quot;BULK&quot;,
      &quot;status&quot;: {
          &quot;$numberInt&quot;: &quot;3&quot;
      },
      &quot;reason&quot;: &quot;No Routing&quot;,
      &quot;channel&quot;: &quot;sms&quot;,
      &quot;messageType&quot;: {
          &quot;$numberInt&quot;: &quot;1&quot;
      },
      &quot;event&quot;: &quot;MT&quot;,
      &quot;content&quot;: &quot;test 132&quot;,
      &quot;credit&quot;: {
          &quot;$numberInt&quot;: &quot;1&quot;
      },
      &quot;msgId&quot;: &quot;&quot;,
      &quot;createdDateTime&quot;: &quot;2020-05-05T13:55:27.743Z&quot;,
      &quot;updatedTime&quot;: &quot;2020-05-05T13:55:27.745Z&quot;,
      &quot;uDate&quot;: &quot;2020-05-05&quot;,
      &quot;operator&quot;: &quot;mobitel&quot;
  }
  </code></pre>
  <p>my query as follows</p>
  <pre><code>db.getCollection('report').aggregate([{
      &quot;$match&quot;: {
          &quot;createdDateTime&quot;: {
              &quot;$gt&quot;: &quot;2020-09-14T00:00:01.000Z&quot;,
              &quot;$lt&quot;: &quot;2020-09-15T23:59:99.999Z&quot;
          },
          &quot;messageType&quot;: {
              &quot;$in&quot;: [1, 2]
          },
          &quot;channel&quot;: {
              &quot;$in&quot;: [&quot;sms&quot;, &quot;viber&quot;, &quot;whatsapp&quot;]
          },
          &quot;accountId&quot;: {
              &quot;$in&quot;: [&quot;ZM&quot;, &quot;KEELLS&quot;]
          }
      }
  }, {
      &quot;$project&quot;: {
          &quot;_id&quot;: 0,
          &quot;channel&quot;: 1,
          &quot;messageType&quot;: 1,
          &quot;accountName&quot;: 1,
          &quot;accountId&quot;: 1,
          &quot;createdDateTime&quot;: 1,
          &quot;uDate&quot;: 1,
          &quot;credit&quot;: 1,
          &quot;status&quot;: 1
      }
  }, {
      &quot;$group&quot;: {
          &quot;_id&quot;: {
              &quot;channel&quot;: &quot;$channel&quot;,
              &quot;messageType&quot;: &quot;$messageType&quot;,
              &quot;accountName&quot;: &quot;$accountName&quot;,
              &quot;accountId&quot;: &quot;$accountId&quot;,
              &quot;filteredDate&quot;: {
                  &quot;$substr&quot;: [&quot;$createdDateTime&quot;, 0, 7]
              },
              &quot;sortDate&quot;: &quot;$uDate&quot;
          },
          &quot;total&quot;: {
              &quot;$sum&quot;: &quot;$credit&quot;
          },
          &quot;send&quot;: {
              &quot;$sum&quot;: {
                  &quot;$cond&quot;: [{
                      &quot;$in&quot;: [&quot;$status&quot;, [2, 15, 1, 14, 6, 17, 4, 5]]
                  }, &quot;$credit&quot;, 0]
              }
          },
          &quot;delivered&quot;: {
              &quot;$sum&quot;: {
                  &quot;$cond&quot;: [{
                          &quot;$in&quot;: [&quot;$status&quot;, [6, 17, 4]]
                      },
                      &quot;$credit&quot;,
                      0
                  ]
              }
          },
          &quot;deliveryFailed&quot;: {
              &quot;$sum&quot;: {
                  &quot;$cond&quot;: [{
                      &quot;$in&quot;: [&quot;$status&quot;, [12, 5]]
                  }, &quot;$credit&quot;, 0]
              }
          },
          &quot;failed&quot;: {
              &quot;$sum&quot;: {
                  &quot;$cond&quot;: [{
                      &quot;$in&quot;: [&quot;$status&quot;, [3]]
                  }, &quot;$credit&quot;, 0]
              }
          },
          &quot;datass&quot;: {
              &quot;$addToSet&quot;: {
                  &quot;channel&quot;: &quot;$channel&quot;,
                  &quot;messageType&quot;: &quot;$messageType&quot;,
                  &quot;accountName&quot;: &quot;$accountName&quot;,
                  &quot;accountId&quot;: &quot;$accountId&quot;,
                  &quot;filteredDate&quot;: {
                      &quot;$substr&quot;: [&quot;$createdDateTime&quot;, 0, 7]
                  },
                  &quot;sortDate&quot;: &quot;$uDate&quot;
              }
          }
      }
  }, {
      &quot;$unwind&quot;: &quot;$datass&quot;
  }, {
      &quot;$project&quot;: {
          &quot;_id&quot;: 0
      }
  }, {
      &quot;$sort&quot;: {
          &quot;datass.sortDate&quot;: -1
      }
  }])
  </code></pre>
  <p>indexes  as follows</p>
  <p>accountId_1 / accountId_1_createdDateTime_-1 / campaignId_-1 / channel_1 / createdDateTime_-1 / messageType_1 / msgId_-1 / msgId_-1_status_1</p>
  <p>I would be appreciated if someone can help me with this</p>
  <p>Thanks</p>