Aggreagtion query optimization

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

1 Like

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.

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'
  },

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

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.

2 Likes

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.