title: How to optimize aggregate mongodb query with group?
body: <p>I have three queries to get needed data by group by currency and price/profit.</p>
  
  <p>One query takes ~1.3 seconds on 230 000 items.</p>
  
  <p>One item looks like this:</p>
  
  <pre><code>{
      "_id" : ObjectId("590e59fca0404a6e5577302b"),
      "make_name" : "Peugeot",
      "model_name" : "307",
      "car_id" : NumberInt("396554354"),
      "title" : "Sell",
      "description" : "Cool",
      "site_name" : "olx.ua",
      "first_registration" : ISODate("2002-01-01T00:00:00.000Z"),
      "fuel" : "Petrol",
      "mileage" : NumberInt("250000"),
      "category" : "Limousine",
      "horse_power" : null,
      "cubic_capacity" : NumberInt("1600"),
      "transmission" : "Manual",
      "price" : NumberInt("5050"),
      "currency" : "USD",
      "negotiable" : true,
      "profit" : NumberInt("-8"),
      "owners_count" : NumberInt("2"),
      "color" : NumberInt("3"),
      "condition" : NumberInt("4"),
      "updated_at" : ISODate("2017-06-01T03:51:34.000Z"),
      "rear_camera" : false,
      "ABS" : false,
      "four_wheel_drive" : false,
      "bluetooth" : false,
      "board_computer" : false,
      "cd_player" : false,
      "electric_mirrors" : false,
      "electric_windows" : true,
      "parking_assistance" : false,
      "handsfree" : false,
      "guarantee" : false,
      "head_up_display" : false,
      "has_inspection" : false,
      "air_conditioning" : false,
      "alloy_wheel_rims" : false,
      "multi_func_steering_wheel" : false,
      "navigation" : false,
      "non_smoking_car" : false,
      "panorama_roof" : false,
      "particle_filter" : false,
      "rain_sensor" : false,
      "full_service_history" : false,
      "power_steering" : false,
      "sunroof" : false,
      "seat_heating" : false,
      "sports_suspension" : false,
      "sports_seats" : false,
      "pre_heating" : false,
      "start_stop" : false,
      "taxi" : false,
      "tax_paid" : true,
      "cruise_control" : false,
      "xenon_headlights" : true,
      "security" : false,
      "sport_package" : false,
      "business" : true,
      "damaged" : false,
      "price_100" : 5000,
      "profit_100" : 0
  },
  </code></pre>
  
  <p>My query is:</p>
  
  <pre><code>db.cars.aggregate([{
      '$match': {
          '$and': [
              { 'first_registration': { '$gte': ISODate("2000-01-01") } },
              { 'first_registration': { '$lte': ISODate("2017-01-01") } },
              { 'price': { '$gte': 0 } },
              { 'price': { '$lte': 60000 } },
              { 'profit': { '$exists': true } },
              { 'profit': { '$gte': -20000 } },
              { 'profit': { '$lte': 30000 } },
              { 'updated_at': { '$gte': ISODate("2017-06-04") } },
              { 'currency': 'USD' },
              { 'damaged': false }]
      }
  },
      {
          '$group': {
              '_id': {
                  'price': {
                      '$subtract': ['$price',
                          { '$mod': ['$price', 100] }]
                  },
                  'profit': { '$subtract': ['$profit', { '$mod': ['$profit', 100] }] }
              },
              'car_id': { '$first': '$car_id' },
              'currency': { '$first': '$currency' },
              'price': { '$first': '$price' },
              'profit': { '$first': '$profit' }
          }
      }])
  </code></pre>
  
  <p>I need to get first item in a group of specified price/profit.</p>
  
  <p>Example: 10 cars have price/profit 100-160 USD, so only one car will be returned for such query, because group(data) point for this car is price 100, profit 100. I hope this works this way.</p>
  
  <p>First "match" query takes around 0.012 seconds to get 150 000 items. </p>
  
  <p>So the issue is in group query, I think.</p>
  
  <p>I tried to pre-build math operations subtract and mod:</p>
  
  <pre><code>db.cars.find({
      'profit': {'$exists': true}, 
      'price_100': {'$exists': false}, }).snapshot().forEach(function(doc){
           db.cars.update({_id:doc._id}, {$set:{
               "price_100":doc.price - (doc.price % 100),
               "profit_100": doc.profit - (doc.profit % 100)
  
           }});
      });
  </code></pre>
  
  <p>Then my query started to look like:</p>
  
  <pre><code>db.cars.aggregate(
      [
          {
      '$match': {
          '$and': [
              { 'first_registration': { '$gte': ISODate("2000-01-01") } },
              { 'first_registration': { '$lte': ISODate("2017-01-01") } },
              { 'price': { '$gte': 0 } },
              { 'price': { '$lte': 60000 } },
              { 'profit': { '$exists': true } },
              { 'profit': { '$gte': -20000 } },
              { 'profit': { '$lte': 30000 } },
              { 'updated_at': { '$gte': ISODate("2017-06-04") } },
              { 'currency': 'USD' },
              { 'damaged': false }]
      }
  },
      {
          '$group': {
              '_id': {
                  'price': '$price_100',
                  'profit': '$profit_100',
              },
              'car_id': { '$first': '$car_id' },
              'currency': { '$first': '$currency' },
              'price': { '$first': '$price' },
              'profit': { '$first': '$profit' }
          }
      }])
  </code></pre>
  
  <p>Unfortunately, <strong>it takes 300 milliseconds more</strong> than original.</p>
  
  <p>Explain to my query:</p>
  
  <pre><code>{
      "stages" : [
          {
              "$cursor" : {
                  "query" : {
                      "$and" : [
                          {
                              "first_registration" : {
                                  "$gte" : ISODate("2000-01-01T00:00:00.000Z")
                              }
                          },
                          {
                              "first_registration" : {
                                  "$lte" : ISODate("2017-01-01T00:00:00.000Z")
                              }
                          },
                          {
                              "price" : {
                                  "$gte" : 0
                              }
                          },
                          {
                              "price" : {
                                  "$lte" : 60000
                              }
                          },
                          {
                              "profit" : {
                                  "$exists" : true
                              }
                          },
                          {
                              "profit" : {
                                  "$gte" : -20000
                              }
                          },
                          {
                              "profit" : {
                                  "$lte" : 30000
                              }
                          },
                          {
                              "updated_at" : {
                                  "$gte" : ISODate("2017-06-04T00:00:00.000Z")
                              }
                          },
                          {
                              "currency" : "USD"
                          },
                          {
                              "damaged" : false
                          }
                      ]
                  },
                  "fields" : {
                      "car_id" : NumberInt("1"),
                      "currency" : NumberInt("1"),
                      "price" : NumberInt("1"),
                      "price_100" : NumberInt("1"),
                      "profit" : NumberInt("1"),
                      "profit_100" : NumberInt("1"),
                      "_id" : NumberInt("0")
                  },
                  "queryPlanner" : {
                      "plannerVersion" : NumberInt("1"),
                      "namespace" : "master_test.cars",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                          "$and" : [
                              {
                                  "currency" : {
                                      "$eq" : "USD"
                                  }
                              },
                              {
                                  "damaged" : {
                                      "$eq" : false
                                  }
                              },
                              {
                                  "first_registration" : {
                                      "$lte" : ISODate("2017-01-01T00:00:00.000Z")
                                  }
                              },
                              {
                                  "price" : {
                                      "$lte" : 60000
                                  }
                              },
                              {
                                  "profit" : {
                                      "$lte" : 30000
                                  }
                              },
                              {
                                  "first_registration" : {
                                      "$gte" : ISODate("2000-01-01T00:00:00.000Z")
                                  }
                              },
                              {
                                  "price" : {
                                      "$gte" : 0
                                  }
                              },
                              {
                                  "profit" : {
                                      "$gte" : -20000
                                  }
                              },
                              {
                                  "updated_at" : {
                                      "$gte" : ISODate("2017-06-04T00:00:00.000Z")
                                  }
                              },
                              {
                                  "profit" : {
                                      "$exists" : true
                                  }
                              }
                          ]
                      },
                      "winningPlan" : {
                          "stage" : "COLLSCAN",
                          "filter" : {
                              "$and" : [
                                  {
                                      "currency" : {
                                          "$eq" : "USD"
                                      }
                                  },
                                  {
                                      "damaged" : {
                                          "$eq" : false
                                      }
                                  },
                                  {
                                      "first_registration" : {
                                          "$lte" : ISODate("2017-01-01T00:00:00.000Z")
                                      }
                                  },
                                  {
                                      "price" : {
                                          "$lte" : 60000
                                      }
                                  },
                                  {
                                      "profit" : {
                                          "$lte" : 30000
                                      }
                                  },
                                  {
                                      "first_registration" : {
                                          "$gte" : ISODate("2000-01-01T00:00:00.000Z")
                                      }
                                  },
                                  {
                                      "price" : {
                                          "$gte" : 0
                                      }
                                  },
                                  {
                                      "profit" : {
                                          "$gte" : -20000
                                      }
                                  },
                                  {
                                      "updated_at" : {
                                          "$gte" : ISODate("2017-06-04T00:00:00.000Z")
                                      }
                                  },
                                  {
                                      "profit" : {
                                          "$exists" : true
                                      }
                                  }
                              ]
                          },
                          "direction" : "forward"
                      },
                      "rejectedPlans" : [ ]
                  }
              }
          },
          {
              "$group" : {
                  "_id" : {
                      "price" : "$price_100",
                      "profit" : "$profit_100"
                  },
                  "car_id" : {
                      "$first" : "$car_id"
                  },
                  "currency" : {
                      "$first" : "$currency"
                  },
                  "price" : {
                      "$first" : "$price"
                  },
                  "profit" : {
                      "$first" : "$profit"
                  }
              }
          }
      ],
      "ok" : 1
  }
  </code></pre>
  
  <p>Why 3 queries one might think? I have 3 currencies: USD, EUR and PLN in my data base, so I do 3 requests. Currently I don't know how to unify the query.</p>
  
  <p><strong>UPDATE FOR NEIL:</strong></p>
  
  <p>After implementing your suggestions, I was able to reduce time from 1.3 seconds to 1 second.</p>
  
  <p>Query looks like:</p>
  
  <pre><code>db.cars.aggregate([{
      '$match': {
          '$and': [
              { 'first_registration': { '$gte': ISODate("2000-01-01"), '$lte': ISODate("2017-01-01") } },
              { 'price': { '$gte': 0, '$lte': 60000 } },
              { 'profit': { '$exists': true, '$gte': -20000, '$lte': 30000  } },
              { 'updated_at': { '$gte': ISODate("2017-06-04") } },
              { 'currency': 'USD' },
              { 'damaged': false }]
      }
  },
      {
          '$group': {
              '_id': {
                  'price': {
                      '$subtract': ['$price',
                          { '$mod': ['$price', 100] }]
                  },
                  'profit': { '$subtract': ['$profit', { '$mod': ['$profit', 100] }] }
              },
              'car_id': { '$first': '$car_id' },
              'currency': { '$first': '$currency' },
              'price': { '$first': '$price' },
              'profit': { '$first': '$profit' }
          }
      }])
  </code></pre>
  
  <p>And explain:</p>
  
  <pre><code>{
      "stages" : [
          {
              "$cursor" : {
                  "query" : {
                      "$and" : [
                          {
                              "first_registration" : {
                                  "$gte" : ISODate("2000-01-01T00:00:00.000Z"),
                                  "$lte" : ISODate("2017-01-01T00:00:00.000Z")
                              }
                          },
                          {
                              "price" : {
                                  "$gte" : 0,
                                  "$lte" : 60000
                              }
                          },
                          {
                              "profit" : {
                                  "$exists" : true,
                                  "$gte" : -20000,
                                  "$lte" : 30000
                              }
                          },
                          {
                              "updated_at" : {
                                  "$gte" : ISODate("2017-06-04T00:00:00.000Z")
                              }
                          },
                          {
                              "currency" : "USD"
                          },
                          {
                              "damaged" : false
                          }
                      ]
                  },
                  "fields" : {
                      "car_id" : NumberInt("1"),
                      "currency" : NumberInt("1"),
                      "price" : NumberInt("1"),
                      "profit" : NumberInt("1"),
                      "_id" : NumberInt("0")
                  },
                  "queryPlanner" : {
                      "plannerVersion" : NumberInt("1"),
                      "namespace" : "master_test.cars",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                          "$and" : [
                              {
                                  "currency" : {
                                      "$eq" : "USD"
                                  }
                              },
                              {
                                  "damaged" : {
                                      "$eq" : false
                                  }
                              },
                              {
                                  "first_registration" : {
                                      "$lte" : ISODate("2017-01-01T00:00:00.000Z")
                                  }
                              },
                              {
                                  "price" : {
                                      "$lte" : 60000
                                  }
                              },
                              {
                                  "profit" : {
                                      "$lte" : 30000
                                  }
                              },
                              {
                                  "first_registration" : {
                                      "$gte" : ISODate("2000-01-01T00:00:00.000Z")
                                  }
                              },
                              {
                                  "price" : {
                                      "$gte" : 0
                                  }
                              },
                              {
                                  "profit" : {
                                      "$gte" : -20000
                                  }
                              },
                              {
                                  "updated_at" : {
                                      "$gte" : ISODate("2017-06-04T00:00:00.000Z")
                                  }
                              },
                              {
                                  "profit" : {
                                      "$exists" : true
                                  }
                              }
                          ]
                      },
                      "winningPlan" : {
                          "stage" : "FETCH",
                          "filter" : {
                              "$and" : [
                                  {
                                      "first_registration" : {
                                          "$lte" : ISODate("2017-01-01T00:00:00.000Z")
                                      }
                                  },
                                  {
                                      "price" : {
                                          "$lte" : 60000
                                      }
                                  },
                                  {
                                      "profit" : {
                                          "$lte" : 30000
                                      }
                                  },
                                  {
                                      "first_registration" : {
                                          "$gte" : ISODate("2000-01-01T00:00:00.000Z")
                                      }
                                  },
                                  {
                                      "price" : {
                                          "$gte" : 0
                                      }
                                  },
                                  {
                                      "profit" : {
                                          "$gte" : -20000
                                      }
                                  },
                                  {
                                      "profit" : {
                                          "$exists" : true
                                      }
                                  }
                              ]
                          },
                          "inputStage" : {
                              "stage" : "IXSCAN",
                              "keyPattern" : {
                                  "updated_at" : 1,
                                  "currency" : 1,
                                  "damaged" : 1
                              },
                              "indexName" : "updated_at_1_currency_1_damaged_1",
                              "isMultiKey" : false,
                              "multiKeyPaths" : {
                                  "updated_at" : [ ],
                                  "currency" : [ ],
                                  "damaged" : [ ]
                              },
                              "isUnique" : false,
                              "isSparse" : false,
                              "isPartial" : false,
                              "indexVersion" : NumberInt("2"),
                              "direction" : "forward",
                              "indexBounds" : {
                                  "updated_at" : [
                                      "[new Date(1496534400000), new Date(9223372036854775807)]"
                                  ],
                                  "currency" : [
                                      "[\"USD\", \"USD\"]"
                                  ],
                                  "damaged" : [
                                      "[false, false]"
                                  ]
                              }
                          }
                      },
                      "rejectedPlans" : [ ]
                  }
              }
          },
          {
              "$group" : {
                  "_id" : {
                      "price" : {
                          "$subtract" : [
                              "$price",
                              {
                                  "$mod" : [
                                      "$price",
                                      {
                                          "$const" : 100
                                      }
                                  ]
                              }
                          ]
                      },
                      "profit" : {
                          "$subtract" : [
                              "$profit",
                              {
                                  "$mod" : [
                                      "$profit",
                                      {
                                          "$const" : 100
                                      }
                                  ]
                              }
                          ]
                      }
                  },
                  "car_id" : {
                      "$first" : "$car_id"
                  },
                  "currency" : {
                      "$first" : "$currency"
                  },
                  "price" : {
                      "$first" : "$price"
                  },
                  "profit" : {
                      "$first" : "$profit"
                  }
              }
          }
      ],
      "ok" : 1
  }
  </code></pre>
  
  <p>Running on pre-built fields price_100 and profit_100 still 1.3 seconds, but now we have 300 ms less for non-prebuilt query, nice!</p>