Aggreagtion query optimization

[Rexcily_Rijo_R1 (Rexcily Rijo R)]

Hi Team,

I have the below aggreagtion query where I am using $addField $lookup and $unwind and I have the Index which is using but it still takes more than 13 Seconds to execute this query.

Can anyone let me know why this is taking so much time for this query and the possible ways to optimize this?

db.getCollection('booked-slot').aggregate([{"$match":{"facility":ObjectId("63ee2e8458b6fd9eb66511ce")}},{"$addFields":{"userId":{"$cond":{"if":{"$eq":["$participant.isPrimary",true]},"then":"$participant.primaryUserId","else":"$participant.secondaryUserId"}},"dateValue":{"$dateFromString":{"dateString":"$date"}}}},{"$lookup":{"from":"users","localField":"userId","foreignField":"_id","as":"user"}},{"$unwind":{"path":"$user","preserveNullAndEmptyArrays":true}}]).explain("executionStats")`

Here is the explain plan for this.

db.getCollection('booked-slot').aggregate([{"$match":{"facility":ObjectId("63ee2e8458b6fd9eb66511ce")}},{"$addFields":{"userId":{"$cond":{"if":{"$eq":["$participant.isPrimary",true]},"then":"$participant.primaryUserId","else":"$participant.secondaryUserId"}},"dateValue":{"$dateFromString":{"dateString":"$date"}}}},{"$lookup":{"from":"users","localField":"userId","foreignField":"_id","as":"user"}},{"$unwind":{"path":"$user","preserveNullAndEmptyArrays":true}}]).explain("executionStats")
{
  explainVersion: '1',
  stages: [
    {
      '$cursor': {
        queryPlanner: {
          namespace: 'test_tuning.booked-slot',
          indexFilterSet: false,
          parsedQuery: { facility: { '$eq': ObjectId("63ee2e8458b6fd9eb66511ce") } },
          queryHash: '1EA0CE2C',
          planCacheKey: 'F5E357E3',
          maxIndexedOrSolutionsReached: false,
          maxIndexedAndSolutionsReached: false,
          maxScansToExplodeReached: false,
          winningPlan: {
            stage: 'FETCH',
            inputStage: {
              stage: 'IXSCAN',
              keyPattern: { facility: 1, state: 1, date: -1, slotStartTime: -1 },
              indexName: 'facility_1_state_1_date_-1_slotStartTime_-1',
              isMultiKey: false,
              multiKeyPaths: { facility: [], state: [], date: [], slotStartTime: [] },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                facility: [
                  "[ObjectId('63ee2e8458b6fd9eb66511ce'), ObjectId('63ee2e8458b6fd9eb66511ce')]"
                ],
                state: [ '[MinKey, MaxKey]' ],
                date: [ '[MaxKey, MinKey]' ],
                slotStartTime: [ '[MaxKey, MinKey]' ]
              }
            }
          },
          rejectedPlans: []
        },
        executionStats: {
          executionSuccess: true,
          nReturned: 95563,
          executionTimeMillis: 13425,
          totalKeysExamined: 95563,
          totalDocsExamined: 95563,
          executionStages: {
            stage: 'FETCH',
            nReturned: 95563,
            executionTimeMillisEstimate: 71,
            works: 95564,
            advanced: 95563,
            needTime: 0,
            needYield: 0,
            saveState: 156,
            restoreState: 156,
            isEOF: 1,
            docsExamined: 95563,
            alreadyHasObj: 0,
            inputStage: {
              stage: 'IXSCAN',
              nReturned: 95563,
              executionTimeMillisEstimate: 31,
              works: 95564,
              advanced: 95563,
              needTime: 0,
              needYield: 0,
              saveState: 156,
              restoreState: 156,
              isEOF: 1,
              keyPattern: { facility: 1, state: 1, date: -1, slotStartTime: -1 },
              indexName: 'facility_1_state_1_date_-1_slotStartTime_-1',
              isMultiKey: false,
              multiKeyPaths: { facility: [], state: [], date: [], slotStartTime: [] },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                facility: [
                  "[ObjectId('63ee2e8458b6fd9eb66511ce'), ObjectId('63ee2e8458b6fd9eb66511ce')]"
                ],
                state: [ '[MinKey, MaxKey]' ],
                date: [ '[MaxKey, MinKey]' ],
                slotStartTime: [ '[MaxKey, MinKey]' ]
              },
              keysExamined: 95563,
              seeks: 1,
              dupsTested: 0,
              dupsDropped: 0
            }
          }
        }
      },
      nReturned: Long("95563"),
      executionTimeMillisEstimate: Long("233")
    },
    {
      '$addFields': {
        userId: {
          '$cond': [
            { '$eq': [ '$participant.isPrimary', { '$const': true } ] },
            '$participant.primaryUserId',
            '$participant.secondaryUserId'
          ]
        },
        dateValue: { '$dateFromString': { dateString: '$date' } }
      },
      nReturned: Long("95563"),
      executionTimeMillisEstimate: Long("236")
    },
    {
      '$lookup': {
        from: 'users',
        as: 'user',
        localField: 'userId',
        foreignField: '_id',
        unwinding: { preserveNullAndEmptyArrays: true }
      },
      totalDocsExamined: Long("95562"),
      totalKeysExamined: Long("95562"),
      collectionScans: Long("0"),
      indexesUsed: [ '_id_' ],
      nReturned: Long("95563"),
      executionTimeMillisEstimate: Long("13418")
    }
  ],
  serverInfo: {
    host: 'ip-10-22-11-89.ap-south-1.compute.internal',
    port: 27017,
    version: '6.0.5',
    gitVersion: 'c9a99c120371d4d4c52cbb15dac34a36ce8d3b1d'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  command: {
    aggregate: 'booked-slot',
    pipeline: [
      { '$match': { facility: ObjectId("63ee2e8458b6fd9eb66511ce") } },
      {
        '$addFields': {
          userId: {
            '$cond': {
              if: { '$eq': [ '$participant.isPrimary', true ] },
              then: '$participant.primaryUserId',
              else: '$participant.secondaryUserId'
            }
          },
          dateValue: { '$dateFromString': { dateString: '$date' } }
        }
      },
      {
        '$lookup': {
          from: 'users',
          localField: 'userId',
          foreignField: '_id',
          as: 'user'
        }
      },
      {
        '$unwind': { path: '$user', preserveNullAndEmptyArrays: true }
      }
    ],
    cursor: {},
    '$db': 'test_tuning'
  },

Please find the document counts in each collection:

booked-slot - 95563
users - 698422

[santimir (santi)]

This is a very minor comment, since I don’t quite remember details. But obviously the slow stage is the look up which is merged (I think) with the unwind stage:

{
      '$lookup': {
       // removed for brevity sake
      },
      totalDocsExamined: Long("95562"),
      totalKeysExamined: Long("95562"),
      collectionScans: Long("0"),
      indexesUsed: [ '_id_' ],
      nReturned: Long("95563"),
      executionTimeMillisEstimate: Long("13418")  // <---- ~13.4s
    }

I’d remove the $unwind and try the query again, is it still that slow?

If that’s still quite slow then the slow part is the look up only.

[Rexcily_Rijo_R1 (Rexcily Rijo R)]

Hi Santimir,

Yes, It’s still taking much time after removing the $unwind stage.

db.getCollection('booked-slot').aggregate([{"$match":{"facility":ObjectId("63ee2e8458b6fd9eb66511ce")}},{"$addFields":{"userId":{"$cond":{"if":{"$eq":["$participant.isPrimary",true]},"then":"$participant.primaryUserId","else":"$participant.secondaryUserId"}},"dateValue":{"$dateFromString":{"dateString":"$date"}}}},{"$lookup":{"from":"users","localField":"userId","foreignField":"_id","as":"user"}}]).explain("executionStats")
{
  explainVersion: '1',
  stages: [
    {
      '$cursor': {
        queryPlanner: {
          namespace: 'test_tuning.booked-slot',
          indexFilterSet: false,
          parsedQuery: { facility: { '$eq': ObjectId("63ee2e8458b6fd9eb66511ce") } },
          queryHash: '1EA0CE2C',
          planCacheKey: 'E057C6F7',
          maxIndexedOrSolutionsReached: false,
          maxIndexedAndSolutionsReached: false,
          maxScansToExplodeReached: false,
          winningPlan: {
            stage: 'FETCH',
            inputStage: {
              stage: 'IXSCAN',
              keyPattern: { facility: 1, state: 1, date: -1, slotStartTime: -1 },
              indexName: 'facility_1_state_1_date_-1_slotStartTime_-1',
              isMultiKey: false,
              multiKeyPaths: { facility: [], state: [], date: [], slotStartTime: [] },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                facility: [
                  "[ObjectId('63ee2e8458b6fd9eb66511ce'), ObjectId('63ee2e8458b6fd9eb66511ce')]"
                ],
                state: [ '[MinKey, MaxKey]' ],
                date: [ '[MaxKey, MinKey]' ],
                slotStartTime: [ '[MaxKey, MinKey]' ]
              }
            }
          },
          rejectedPlans: [
            {
              stage: 'FETCH',
              inputStage: {
                stage: 'IXSCAN',
                keyPattern: {
                  facility: 1,
                  practitioner: 1,
                  facilityPractitioner: 1,
                  state: 1,
                  date: 1,
                  slotStartTime: -1
                },
                indexName: 'facility_1_practitioner_1_facilityPractitioner_1_state_1_date_1_slotStartTime_-1',
                isMultiKey: false,
                multiKeyPaths: {
                  facility: [],
                  practitioner: [],
                  facilityPractitioner: [],
                  state: [],
                  date: [],
                  slotStartTime: []
                },
                isUnique: false,
                isSparse: false,
                isPartial: false,
                indexVersion: 2,
                direction: 'forward',
                indexBounds: {
                  facility: [
                    "[ObjectId('63ee2e8458b6fd9eb66511ce'), ObjectId('63ee2e8458b6fd9eb66511ce')]"
                  ],
                  practitioner: [ '[MinKey, MaxKey]' ],
                  facilityPractitioner: [ '[MinKey, MaxKey]' ],
                  state: [ '[MinKey, MaxKey]' ],
                  date: [ '[MinKey, MaxKey]' ],
                  slotStartTime: [ '[MaxKey, MinKey]' ]
                }
              }
            }
          ]
        },
        executionStats: {
          executionSuccess: true,
          nReturned: 95563,
          executionTimeMillis: 13411,
          totalKeysExamined: 95563,
          totalDocsExamined: 95563,
          executionStages: {
            stage: 'FETCH',
            nReturned: 95563,
            executionTimeMillisEstimate: 100,
            works: 95564,
            advanced: 95563,
            needTime: 0,
            needYield: 0,
            saveState: 156,
            restoreState: 156,
            isEOF: 1,
            docsExamined: 95563,
            alreadyHasObj: 0,
            inputStage: {
              stage: 'IXSCAN',
              nReturned: 95563,
              executionTimeMillisEstimate: 47,
              works: 95564,
              advanced: 95563,
              needTime: 0,
              needYield: 0,
              saveState: 156,
              restoreState: 156,
              isEOF: 1,
              keyPattern: { facility: 1, state: 1, date: -1, slotStartTime: -1 },
              indexName: 'facility_1_state_1_date_-1_slotStartTime_-1',
              isMultiKey: false,
              multiKeyPaths: { facility: [], state: [], date: [], slotStartTime: [] },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: {
                facility: [
                  "[ObjectId('63ee2e8458b6fd9eb66511ce'), ObjectId('63ee2e8458b6fd9eb66511ce')]"
                ],
                state: [ '[MinKey, MaxKey]' ],
                date: [ '[MaxKey, MinKey]' ],
                slotStartTime: [ '[MaxKey, MinKey]' ]
              },
              keysExamined: 95563,
              seeks: 1,
              dupsTested: 0,
              dupsDropped: 0
            }
          }
        }
      },
      nReturned: Long("95563"),
      executionTimeMillisEstimate: Long("243")
    },
    {
      '$addFields': {
        userId: {
          '$cond': [
            { '$eq': [ '$participant.isPrimary', { '$const': true } ] },
            '$participant.primaryUserId',
            '$participant.secondaryUserId'
          ]
        },
        dateValue: { '$dateFromString': { dateString: '$date' } }
      },
      nReturned: Long("95563"),
      executionTimeMillisEstimate: Long("260")
    },
    {
      '$lookup': {
        from: 'users',
        as: 'user',
        localField: 'userId',
        foreignField: '_id'
      },
      totalDocsExamined: Long("95563"),
      totalKeysExamined: Long("95563"),
      collectionScans: Long("0"),
      indexesUsed: [ '_id_' ],
      nReturned: Long("95563"),
      executionTimeMillisEstimate: Long("13395")
    }
  ],
  serverInfo: {
    host: 'ip-10-22-11-89.ap-south-1.compute.internal',
    port: 27017,
    version: '6.0.5',
    gitVersion: 'c9a99c120371d4d4c52cbb15dac34a36ce8d3b1d'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  command: {
    aggregate: 'booked-slot',
    pipeline: [
      { '$match': { facility: ObjectId("63ee2e8458b6fd9eb66511ce") } },
      {
        '$addFields': {
          userId: {
            '$cond': {
              if: { '$eq': [ '$participant.isPrimary', true ] },
              then: '$participant.primaryUserId',
              else: '$participant.secondaryUserId'
            }
          },
          dateValue: { '$dateFromString': { dateString: '$date' } }
        }
      },
      {
        '$lookup': {
          from: 'users',
          localField: 'userId',
          foreignField: '_id',
          as: 'user'
        }
      }
    ],
    cursor: {},
    '$db': 'test_tuning'
  },

[steevej (Steeve Juneau)]

Here is a link to something that looks a lot like exactly the same issue but being worked on by different people.

Mongodb Aggregation query Optimization

John, in the explain output which I shared its mentioned returned documents as 99099, so i think its the full result, I even verified it on compass and got the result in same time. Here is the code through which i ran my query - async function executeQuery() { const db = await connect(); const Slot = db.collection('booked-slot'); const output = await Slot.aggregate([ { $addFields: { dateValue: { $dateFromString: { …

I am skeptical of use-cases that needs to return 95K documents back to the application. Why do you need that much documents? What is your use-case exactly? Is this a frequent use-case?

The $lookup seems to perform well since the _id index is used. I suspect most of the time is spent reading documents from disk. What is the average size of documents from the users collection? What are the characteristics of the server? Is the application making the query running on the same machine? Do you need all the fields from users? A pipeline with a $project in the $lookup might be appropriate. This with a index on _id and the $project-ed fields might be able to reduce totalDocsExamined hence the disk I/O.

Sample, non redacted, documents from both collection might help understand.

You should store your dates as date rather than string. You would then not need to do $dateFromString. And dates as date take less space and are faster than the string counter parts.

Sometimes, aggregating from the $lookup-ed collection is faster that the other way around as it might reduce the number of duplicate in the result.

[santimir (santi)]

Makes sense since the nReturned isn’t larger before and after unwinding, but I wanted to be sure.

I thought that some sort of pagination could be useful, splitting the aggregation and then querying the lookup stage by steps.

But seems relevant that you take a look at steveej comment first.