Enhance Performance for 1 Million records

Working with Data
,
1

Hello,
I am using a collection with aprox 0.5 Million documents inside the collection.
I just want to get the sum of the some of columns on the whole collection with one lookup where I need to filter based on that lookup collection.
My concern is that for only 0.5 million records the query take about 1 min time to execute it.
I have also used the indexed on the columns but still it takes this much time.
What is the issue with and what’s wrong with it?
Is there any suggestion or idea how to improve the performance?

My Collections:

  1. Journey [ Contains 0.5 Million records ]
  2. Vehicles [ Lookup collection ] [ where I need to check the status must match with the given array ]
  3. Group by journey.time and sum of columns
3

Hello @Ghanshyam_Ashra ,

Welcome to The MongoDB Community Forums! :wave:

Could you please help me with below details to get better understanding of your query slowness?

  • What is the exact version of your MongoDB?
  • Please run your query with explain in executionStats mode (e.g. `db.collection.explain(‘executionStats’).aggregate(…)) and share the output.
  • Some sample documents
  • Query you are using
  • What are the indexes you define in all the relevant collections

Along with information about the query and collections, could you also provide some information about your hardware specifications and server

  • What are the hardware specifications?
  • What is your deployment topology (e.g. standalone, replica set, sharded cluster)?
  • Is your mongod server sharing resources with other processes such as other applications running on same server, other server processes or even other mongod processes?

Regards,
Tarun

1 Like
5

@Tarun_Gaur Thanks for the attention.

My Mongodb version is: MongoDB server version: 4.2.23-rc0. MongoDB compass is version 1.33.0.

Explain Response:


{ stages:

   [ { '$cursor':

        { query:

           { start_time:

              { '$gte': 2022-09-06T04:28:31.779Z,

                '$lte': 2022-10-06T04:28:31.779Z } },

          fields:

           { distance: 1,

             harsh_breaking_counts: 1,

             idling_counts: 1,

             rpm_counts: 1,

             speeding_incident_counts: 1,

             start_time: 1,

             vehicle_id: 1,

             _id: 0 },

          queryPlanner:

           { plannerVersion: 1,

             namespace: 'kpi_rps.journeys',

             indexFilterSet: false,

             parsedQuery:

              { '$and':

                 [ { start_time: { '$lte': 2022-10-06T04:28:31.779Z } },

                   { start_time: { '$gte': 2022-09-06T04:28:31.779Z } } ] },

             queryHash: '1367AE20',

             planCacheKey: '4A44DFBB',

             winningPlan:

              { stage: 'FETCH',

                inputStage:

                 { stage: 'IXSCAN',

                   keyPattern: { start_time: 1 },

                   indexName: 'start_time_1',

                   isMultiKey: false,

                   multiKeyPaths: { start_time: [] },

                   isUnique: false,

                   isSparse: false,

                   isPartial: false,

                   indexVersion: 2,

                   direction: 'forward',

                   indexBounds: { start_time: [ '[{ $date: { $numberLong: "1662438511779" } }, { $date: { $numberLong: "1665030511779" } }]' ] } } },

             rejectedPlans: [] },

          executionStats:

           { executionSuccess: true,

             nReturned: 0,

             executionTimeMillis: 2,

             totalKeysExamined: 0,

             totalDocsExamined: 0,

             executionStages:

              { stage: 'FETCH',

                nReturned: 0,

                executionTimeMillisEstimate: 0,

                works: 1,

                advanced: 0,

                needTime: 0,

                needYield: 0,

                saveState: 1,

                restoreState: 1,

                isEOF: 1,

                docsExamined: 0,

                alreadyHasObj: 0,

                inputStage:

                 { stage: 'IXSCAN',

                   nReturned: 0,

                   executionTimeMillisEstimate: 0,

                   works: 1,

                   advanced: 0,

                   needTime: 0,

                   needYield: 0,

                   saveState: 1,

                   restoreState: 1,

                   isEOF: 1,

                   keyPattern: { start_time: 1 },

                   indexName: 'start_time_1',

                   isMultiKey: false,

                   multiKeyPaths: { start_time: [] },

                   isUnique: false,

                   isSparse: false,

                   isPartial: false,

                   indexVersion: 2,

                   direction: 'forward',

                   indexBounds: { start_time: [ '[{ $date: { $numberLong: "1662438511779" } }, { $date: { $numberLong: "1665030511779" } }]' ] },

                   keysExamined: 0,

                   seeks: 1,

                   dupsTested: 0,

                   dupsDropped: 0 } } } } },

     { '$lookup':

        { from: 'vehicles',

          as: 'vehicle',

          localField: 'vehicle_id',

          foreignField: 'id',

          unwinding: { preserveNullAndEmptyArrays: false },

          matching: { status: { '$in': [ 'Roadworthy', 'Roadworthy (with defects)', 'VOR' ] } } } },

     { '$group':

        { _id: { '$dateToString': { date: '$start_time', format: { '$const': '%Y-%m-%d' } } },

          total_distance: { '$sum': '$distance' },

          total_speeding_incidents: { '$sum': '$speeding_incident_counts' },

          total_breaking_incidents: { '$sum': '$harsh_breaking_counts' },

          total_idlinging_incidents: { '$sum': '$idling_counts' },

          total_rpm_incidents: { '$sum': '$rpm_counts' } } } ],

  serverInfo:

   { host: 'DESKTOP-1IBPCFM',

     port: 27017,

     version: '4.2.23-rc0',

     gitVersion: 'cf91e1fbb5f45590d8e356e57522648381fea93c' },

  ok: 1 }

Journeys Documents :


{ _id: ObjectId("631360201fe5008c790afe10"),

  id: 1,

  vehicle_id: 181,

  user_id: 1,

  start_time: 2022-02-07T12:37:11.000Z,

  end_time: 2022-02-07T12:43:19.000Z,

  start_lat: Decimal128("51.52837"),

  start_lon: Decimal128("-3.07873"),

  end_lat: Decimal128("51.530338"),

  end_lon: Decimal128("-3.102211"),

  engine_duration: 368,

  idle_duration: 128,

  fuel: Decimal128("0.43"),

  co2: Decimal128("1.10"),

  distance: 1890,

  odometer: 17370605,

  odometer_start: 17368668,

  odometer_end: 17370605,

  avg_speed: 3.44,

  max_speed: 12,

  incident_count: 0,

  harsh_breaking_count: 0,

  harsh_acceleration_count: 0,

  harsh_cornering_count: 0,

  speeding_count: 0,

  speeding_incident_count: null,

  rpm_count: 0,

  idling_count: 0,

  updated_at: 2022-09-03T14:09:36.838Z,

  created_at: 2022-09-03T14:09:36.838Z }

{ _id: ObjectId("631360201fe5008c790afe11"),

  id: 2,

  vehicle_id: 181,

  user_id: 1,

  start_time: 2022-02-07T12:45:26.000Z,

  end_time: 2022-02-07T13:34:13.000Z,

  start_lat: Decimal128("51.53034"),

  start_lon: Decimal128("-3.10221"),

  end_lat: Decimal128("51.656443"),

  end_lon: Decimal128("-3.337073"),

  engine_duration: 2927,

  idle_duration: 1261,

  fuel: Decimal128("2.98"),

  co2: Decimal128("7.70"),

  distance: 32559,

  odometer: 17403336,

  odometer_start: 17370605,

  odometer_end: 17403336,

  avg_speed: 8.81,

  max_speed: 26,

  incident_count: 2,

  harsh_breaking_count: 0,

  harsh_acceleration_count: 0,

  harsh_cornering_count: 0,

  speeding_count: 0,

  speeding_incident_count: null,

  rpm_count: 0,

  idling_count: 2,

  updated_at: 2022-09-03T14:09:36.839Z,

  created_at: 2022-09-03T14:09:36.839Z }

{ _id: ObjectId("631360201fe5008c790afe12"),

  id: 3,

  vehicle_id: 181,

  user_id: 1,

  start_time: 2022-02-07T13:48:58.000Z,

  end_time: 2022-02-07T13:59:11.000Z,

  start_lat: Decimal128("51.65644"),

  start_lon: Decimal128("-3.33707"),

  end_lat: Decimal128("51.656464"),

  end_lon: Decimal128("-3.337021"),

  engine_duration: 613,

  idle_duration: 608,

  fuel: Decimal128("0.00"),

  co2: Decimal128("0.00"),

  distance: 0,

  odometer: 17403336,

  odometer_start: 17403336,

  odometer_end: 17403336,

  avg_speed: 0,

  max_speed: 0,

  incident_count: 1,

  harsh_breaking_count: 0,

  harsh_acceleration_count: 0,

  harsh_cornering_count: 0,

  speeding_count: 0,

  speeding_incident_count: null,

  rpm_count: 0,

  idling_count: 1,

  updated_at: 2022-09-03T14:09:36.840Z,

  created_at: 2022-09-03T14:09:36.840Z }

{ _id: ObjectId("631360201fe5008c790afe13"),

  id: 4,

  vehicle_id: 181,

  user_id: 1,

  start_time: 2022-02-07T14:30:12.000Z,

  end_time: 2022-02-07T14:47:54.000Z,

  start_lat: Decimal128("51.65646"),

  start_lon: Decimal128("-3.33702"),

  end_lat: Decimal128("51.696191"),

  end_lon: Decimal128("-3.346751"),

  engine_duration: 1062,

  idle_duration: 248,

  fuel: Decimal128("0.59"),

  co2: Decimal128("1.50"),

  distance: 9755,

  odometer: 17413179,

  odometer_start: 17403336,

  odometer_end: 17413179,

  avg_speed: 8.05,

  max_speed: 21,

  incident_count: 0,

  harsh_breaking_count: 0,

  harsh_acceleration_count: 0,

  harsh_cornering_count: 0,

  speeding_count: 0,

  speeding_incident_count: null,

  rpm_count: 0,

  idling_count: 0,

  updated_at: 2022-09-03T14:09:36.841Z,

  created_at: 2022-09-03T14:09:36.841Z }

{ _id: ObjectId("631360201fe5008c790afe14"),

  id: 5,

  vehicle_id: 181,

  user_id: 1,

  start_time: 2022-02-07T15:11:53.000Z,

  end_time: 2022-02-07T15:14:38.000Z,

  start_lat: Decimal128("51.69619"),

  start_lon: Decimal128("-3.34675"),

  end_lat: Decimal128("51.696226"),

  end_lon: Decimal128("-3.346785"),

  engine_duration: 165,

  idle_duration: 0,

  fuel: Decimal128("0.00"),

  co2: Decimal128("0.00"),

  distance: 0,

  odometer: 17413179,

  odometer_start: 17413179,

  odometer_end: 17413179,

  avg_speed: 0,

  max_speed: 0,

  incident_count: 0,

  harsh_breaking_count: 0,

  speeding_incident_count: null,

  rpm_count: 0,

  idling_count: 0 }

Vehicles Documents:


{ _id: ObjectId("6313601f1fe5008c790afbc5"),

  id: 1,

  status: 'Roadworthy',

  telematics_status: 'tm8.gps.ign.off',

  last_location_lat: Decimal128("51.353667"),

  last_location_lon: Decimal128("-0.482931"),

  last_location_time: 2022-09-02T17:38:59.000Z,

  updated_at: 2022-09-03T14:09:35.564Z,

  created_at: 2022-09-03T14:09:35.564Z }

{ _id: ObjectId("6313601f1fe5008c790afbc6"),

  id: 2,

  status: 'Roadworthy',

  telematics_status: 'tm8.gps.ign.off',

  last_location_lat: Decimal128("56.206825"),

  last_location_lon: Decimal128("-3.17141"),

  last_location_time: 2022-06-14T10:27:16.000Z,

  updated_at: 2022-09-03T14:09:35.566Z,

  created_at: 2022-09-03T14:09:35.566Z }

{ _id: ObjectId("6313601f1fe5008c790afbc7"),

  id: 3,

  status: 'Roadworthy',

  telematics_status: null,

  last_location_lat: null,

  last_location_lon: null,

  last_location_time: null,

  updated_at: 2022-09-03T14:09:35.566Z,

  created_at: 2022-09-03T14:09:35.566Z }

{ _id: ObjectId("6313601f1fe5008c790afbc8"),

  id: 4,

  status: 'Archived',

  telematics_status: null,

  last_location_lat: null,

  last_location_lon: null,

  last_location_time: null,

  updated_at: 2022-09-03T14:09:35.567Z,

  created_at: 2022-09-03T14:09:35.567Z }

{ _id: ObjectId("6313601f1fe5008c790afbc9"),

  id: 5,

  status: 'Roadworthy',

  telematics_status: 'tm8.gps.ign.off',

  last_location_lat: Decimal128("53.382753"),

  last_location_lon: Decimal128("-2.189482"),

  last_location_time: 2022-09-02T11:56:21.000Z,

  updated_at: 2022-09-03T14:09:35.568Z,

  created_at: 2022-09-03T14:09:35.568Z }

My Query that takes approx 1 min:


db.journeys.aggregate([

  {

    "$match": {

      "start_time": {

        "$gte": { "$date": { "$numberLong": "1662440173747" } },

        "$lte": { "$date": { "$numberLong": "1665032173747" } }

      }

    }

  },

  {

    "$lookup": {

      "from": "vehicles",

      "localField": "vehicle_id",

      "foreignField": "id",

      "as": "vehicle"

    }

  },

  { "$unwind": { "path": "$vehicle" } },

  {

    "$match": {

      "vehicle.status": {

        "$in": ["Roadworthy", "Roadworthy (with defects)", "VOR"]

      }

    }

  },

  {

    "$group": {

      "_id": {

        "$dateToString": { "format": "%Y-%m-%d", "date": "$start_time" }

      },

      "total_distance": { "$sum": "$distance" },

      "total_speeding_incidents": { "$sum": "$speeding_incident_counts" },

      "total_breaking_incidents": { "$sum": "$harsh_breaking_counts" },

      "total_idlinging_incidents": { "$sum": "$idling_counts" },

      "total_rpm_incidents": { "$sum": "$rpm_counts" }

    }

  }

])

Indexes applied on all the columns that used inside query:

  1. Start_time

  2. speeding_incident_count

  3. harsh_breaking_count

  4. distance

  5. idling_count

  6. rpm_count

And other fields applied Indexes and also it is used when query run.

Currently it is in my local system and still it takes time to load. My system has 8 GB RAM and using i5 processor.

Thanks.

6

@Ghanshyam_Ashra , why is most of the parameters 0 in executionStats of Explain Response?
Just an observation that the only index used in this is start_time_1.

7

So what is the issue there tat this statistics come as Zero?

8

@Tarun_Gaur
Total documents scanned: 82k
Estimated time: 14810 MS ( Approx 14 seconds )
It seems too much low performance

Explain Stats:

{
        "stages" : [
                {
                        "$cursor" : {
                                "query" : {
                                        "start_time" : {
                                                "$gte" : ISODate("2022-08-01T00:00:00Z"),
                                                "$lte" : ISODate("2022-09-20T00:00:00Z")
                                        }
                                },
                                "fields" : {
                                        "COLUMN_1" : 1,
                                        "COLUMN_2" : 1,
                                        "COLUMN_3" : 1,
                                        "COLUMN_4" : 1,
                                        "COLUMN_5" : 1,
                                        "COLUMN_6" : 1,
                                        "COLUMN_7" : 1,
                                        "collection_2_id" : 1,
                                        "_id" : 0
                                },
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "db.journeys_collection",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "$and" : [
                                                        {
                                                                "start_time" : {
                                                                        "$lte" : ISODate("2022-09-20T00:00:00Z")
                                                                }
                                                        },
                                                        {
                                                                "start_time" : {
                                                                        "$gte" : ISODate("2022-08-01T00:00:00Z")
                                                                }
                                                        }
                                                ]
                                        },
                                        "queryHash" : "1367AE20",
                                        "planCacheKey" : "4A44DFBB",
                                        "winningPlan" : {
                                                "stage" : "FETCH",
                                                "inputStage" : {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "start_time" : 1
                                                        },
                                                        "indexName" : "start_time_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "start_time" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "start_time" : [
                                                                        "[new Date(1659312000000), new Date(1663632000000)]"
                                                                ]
                                                        }
                                                }
                                        },
                                        "rejectedPlans" : [ ]
                                },
                                "executionStats" : {
                                        "executionSuccess" : true,
                                        "nReturned" : 82120,
                                        "executionTimeMillis" : 14810,
                                        "totalKeysExamined" : 82120,
                                        "totalDocsExamined" : 82120,
                                        "executionStages" : {
                                                "stage" : "FETCH",
                                                "nReturned" : 82120,
                                                "executionTimeMillisEstimate" : 5,
                                                "works" : 82121,
                                                "advanced" : 82120,
                                                "needTime" : 0,
                                                "needYield" : 0,
                                                "saveState" : 656,
                                                "restoreState" : 656,
                                                "isEOF" : 1,
                                                "docsExamined" : 82120,
                                                "alreadyHasObj" : 0,
                                                "inputStage" : {
                                                        "stage" : "IXSCAN",
                                                        "nReturned" : 82120,
                                                        "executionTimeMillisEstimate" : 2,
                                                        "works" : 82121,
                                                        "advanced" : 82120,
                                                        "needTime" : 0,
                                                        "needYield" : 0,
                                                        "saveState" : 656,
                                                        "restoreState" : 656,
                                                        "isEOF" : 1,
                                                        "keyPattern" : {
                                                                "start_time" : 1
                                                        },
                                                        "indexName" : "start_time_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "start_time" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "start_time" : [
                                                                        "[new Date(1659312000000), new Date(1663632000000)]"
                                                                ]
                                                        },
                                                        "keysExamined" : 82120,
                                                        "seeks" : 1,
                                                        "dupsTested" : 0,
                                                        "dupsDropped" : 0
                                                }
                                        }
                                }
                        }
                },
                {
                        "$lookup" : {
                                "from" : "COLLECTION_2",
                                "as" : "result",
                                "localField" : "collection_2_id",
                                "foreignField" : "id",
                                "unwinding" : {
                                        "preserveNullAndEmptyArrays" : false
                                },
                                "matching" : {
                                        "status" : {
                                                "$in" : [
                                                        "STATUS_1",
                                                        "STATUS_2",
                                                        "STATUS_3"
                                                ]
                                        }
                                }
                        }
                },
                {
                        "$group" : {
                                "_id" : {
                                        "$dateToString" : {
                                                "date" : "$start_time",
                                                "format" : {
                                                        "$const" : "%Y-%m-%d"
                                                }
                                        }
                                },
                                "total_COLUMN_1_envents" : {
                                        "$sum" : "$COLUMN_1"
                                },
                                "total_COLUMN_2_envents" : {
                                        "$sum" : "$COLUMN_2"
                                },
                                "total_COLUMN_3_envents" : {
                                        "$sum" : "$COLUMN_3"
                                },
                                "total_COLUMN_4_envents" : {
                                        "$sum" : "$COLUMN_4"
                                },
                                "total_COLUMN_5_envents" : {
                                        "$sum" : "$COLUMN_5"
                                },
                                "total_COLUMN_6_envents" : {
                                        "$sum" : "$COLUMN_6"
                                }
                        }
                },
                {
                        "$sort" : {
                                "sortKey" : {
                                        "_id" : 1
                                }
                        }
                }
        ],
        "serverInfo" : {
                "host" : "DESKTOP-KIPRKDI",
                "port" : 27017,
                "version" : "4.2.23-rc0",
                "gitVersion" : "cf91e1fbb5f45590d8e356e57522648381fea93c"
        },
        "ok" : 1
}

Thanks.

9

@Tarun_Gaur
I have added new explain result here.
And it seems that $lookup takes time to execute the relationship.
Is there any possible way to enhance the performance with $lookup and $match on multiple fields with $lookup data?

Thanks.