I have the below aggregation query which is using the index but it still take more than 23sec to execute.Can anyone please let me how what more i can tune here to reduce the executiontime?
db.getCollection('booked-slot').explain("executionStats").aggregate([{"$match":{"facility":ObjectId("63ee2e8458b6fd9eb66511ce")}},{"$addFields":{"userId":{"$cond":{"if":{"$eq":["$participant.isPrimary",true]},"then":"$participant.primaryUserId","else":"$participant.secondaryUserId"}},"dateValue":{"$dateFromString":{"dateString":"$date"}}}},{"$match":{}},{"$lookup":{"from":"users","localField":"userId","foreignField":"_id","as":"user"}},{"$unwind":{"path":"$user","preserveNullAndEmptyArrays":true}},{"$lookup":{"from":"practitioners","localField":"practitioner","foreignField":"_id","as":"practitioner"}},{"$unwind":{"path":"$practitioner","preserveNullAndEmptyArrays":true}},{"$lookup":{"from":"facility-practitioners","localField":"facilityPractitioner","foreignField":"_id","as":"facilityPractitioners"}},{"$unwind":{"path":"$facilityPractitioners","preserveNullAndEmptyArrays":true}},{"$addFields":{"userName":{"$toLower":{"$concat":["$user.name.first"," ","$user.name.last"]}},"photo":"$user.profile.picture"}},{"$match":{"$and":[{"practitioner._id":ObjectId("64f97e5d7884cee0c928a44c")},{"$or":[{"state":{"$in":["CANCELLED","FULLFILLED","UN_ATTENDED","USER_NO_SHOW","PRACTITIONER_NO_SHOW","NETWORK_ERROR","INCOMPLETE"]}}]}]}},{"$lookup":{"from":"master-data-call-status","localField":"appointmentUpdateReason","foreignField":"_id","as":"callStatus"}},{"$unwind":{"path":"$callStatus","preserveNullAndEmptyArrays":true}},{"$sort":{"date":-1,"slotStartTime":-1,"userName":1}},{"$project":{"_id":1,"prescription":1,"paymentOrder":1,"user":{"name":1,"_id":1},"photo":1,"practitioner":{"name":1,"_id":1},"consultationType":1,"date":1,"slotStartTime":1,"facilityPractitioner":1,"records":1,"createdAt":1,"updatedAt":1,"state":1,"updatedBy":1,"callStatus":{"code":1}}},{"$skip":0},{"$limit":10}])
Below is my exlain plan for the above query.
{
explainVersion: '1',
stages: [
{
'$cursor': {
queryPlanner: {
namespace: 'test_tuning.booked-slot',
indexFilterSet: false,
parsedQuery: {
'$and': [
{
facility: { '$eq': ObjectId("63ee2e8458b6fd9eb66511ce") }
},
{
state: {
'$in': [
'CANCELLED',
'FULLFILLED',
'INCOMPLETE',
'NETWORK_ERROR',
'PRACTITIONER_NO_SHOW',
'UN_ATTENDED',
'USER_NO_SHOW'
]
}
}
]
},
queryHash: 'F3E52FD7',
planCacheKey: 'AEC7120F',
maxIndexedOrSolutionsReached: false,
maxIndexedAndSolutionsReached: false,
maxScansToExplodeReached: false,
winningPlan: {
stage: 'PROJECTION_DEFAULT',
transformBy: {
_id: 1,
appointmentUpdateReason: 1,
'callStatus.code': 1,
consultationType: 1,
createdAt: 1,
date: 1,
facilityPractitioner: 1,
'participant.isPrimary': 1,
'participant.primaryUserId': 1,
'participant.secondaryUserId': 1,
paymentOrder: 1,
photo: 1,
practitioner: 1,
prescription: 1,
records: 1,
slotStartTime: 1,
state: 1,
updatedAt: 1,
updatedBy: 1,
'user._id': 1,
'user.name': 1,
'user.profile.picture': 1,
userId: 1,
userName: 1
},
inputStage: {
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: [
'["CANCELLED", "CANCELLED"]',
'["FULLFILLED", "FULLFILLED"]',
'["INCOMPLETE", "INCOMPLETE"]',
'["NETWORK_ERROR", "NETWORK_ERROR"]',
'["PRACTITIONER_NO_SHOW", "PRACTITIONER_NO_SHOW"]',
'["UN_ATTENDED", "UN_ATTENDED"]',
'["USER_NO_SHOW", "USER_NO_SHOW"]'
],
date: [ '[MaxKey, MinKey]' ],
slotStartTime: [ '[MaxKey, MinKey]' ]
}
}
}
},
rejectedPlans: []
},
executionStats: {
executionSuccess: true,
nReturned: 87251,
executionTimeMillis: 23902,
totalKeysExamined: 87252,
totalDocsExamined: 87251,
executionStages: {
stage: 'PROJECTION_DEFAULT',
nReturned: 87251,
executionTimeMillisEstimate: 238,
works: 87253,
advanced: 87251,
needTime: 1,
needYield: 0,
saveState: 102,
restoreState: 102,
isEOF: 1,
transformBy: {
_id: 1,
appointmentUpdateReason: 1,
'callStatus.code': 1,
consultationType: 1,
createdAt: 1,
date: 1,
facilityPractitioner: 1,
'participant.isPrimary': 1,
'participant.primaryUserId': 1,
'participant.secondaryUserId': 1,
paymentOrder: 1,
photo: 1,
practitioner: 1,
prescription: 1,
records: 1,
slotStartTime: 1,
state: 1,
updatedAt: 1,
updatedBy: 1,
'user._id': 1,
'user.name': 1,
'user.profile.picture': 1,
userId: 1,
userName: 1
},
inputStage: {
stage: 'FETCH',
nReturned: 87251,
executionTimeMillisEstimate: 113,
works: 87253,
advanced: 87251,
needTime: 1,
needYield: 0,
saveState: 102,
restoreState: 102,
isEOF: 1,
docsExamined: 87251,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 87251,
executionTimeMillisEstimate: 62,
works: 87253,
advanced: 87251,
needTime: 1,
needYield: 0,
saveState: 102,
restoreState: 102,
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: [
'["CANCELLED", "CANCELLED"]',
'["FULLFILLED", "FULLFILLED"]',
'["INCOMPLETE", "INCOMPLETE"]',
'["NETWORK_ERROR", "NETWORK_ERROR"]',
'["PRACTITIONER_NO_SHOW", "PRACTITIONER_NO_SHOW"]',
'["UN_ATTENDED", "UN_ATTENDED"]',
'["USER_NO_SHOW", "USER_NO_SHOW"]'
],
date: [ '[MaxKey, MinKey]' ],
slotStartTime: [ '[MaxKey, MinKey]' ]
},
keysExamined: 87252,
seeks: 2,
dupsTested: 0,
dupsDropped: 0
}
}
}
}
},
nReturned: Long("87251"),
executionTimeMillisEstimate: Long("473")
},
{
'$addFields': {
userId: {
'$cond': [
{ '$eq': [ '$participant.isPrimary', { '$const': true } ] },
'$participant.primaryUserId',
'$participant.secondaryUserId'
]
},
dateValue: { '$dateFromString': { dateString: '$date' } }
},
nReturned: Long("87251"),
executionTimeMillisEstimate: Long("493")
},
{
'$lookup': {
from: 'users',
as: 'user',
localField: 'userId',
foreignField: '_id',
unwinding: { preserveNullAndEmptyArrays: true }
},
totalDocsExamined: Long("87250"),
totalKeysExamined: Long("87250"),
collectionScans: Long("0"),
indexesUsed: [ '_id_' ],
nReturned: Long("87251"),
executionTimeMillisEstimate: Long("12126")
},
{
'$lookup': {
from: 'practitioners',
as: 'practitioner',
localField: 'practitioner',
foreignField: '_id',
unwinding: { preserveNullAndEmptyArrays: true }
},
totalDocsExamined: Long("87250"),
totalKeysExamined: Long("87250"),
collectionScans: Long("0"),
indexesUsed: [ '_id_' ],
nReturned: Long("87251"),
executionTimeMillisEstimate: Long("22935")
},
{
'$match': {
'practitioner._id': { '$eq': ObjectId("64f97e5d7884cee0c928a44c") }
},
nReturned: Long("3850"),
executionTimeMillisEstimate: Long("22948")
},
{
'$lookup': {
from: 'facility-practitioners',
as: 'facilityPractitioners',
localField: 'facilityPractitioner',
foreignField: '_id',
unwinding: { preserveNullAndEmptyArrays: true }
},
totalDocsExamined: Long("3849"),
totalKeysExamined: Long("3849"),
collectionScans: Long("0"),
indexesUsed: [ '_id_' ],
nReturned: Long("3850"),
executionTimeMillisEstimate: Long("23438")
},
{
'$addFields': {
userName: {
'$toLower': [
{
'$concat': [
'$user.name.first',
{ '$const': ' ' },
'$user.name.last'
]
}
]
},
photo: '$user.profile.picture'
},
nReturned: Long("3850"),
executionTimeMillisEstimate: Long("23438")
},
{
'$lookup': {
from: 'master-data-call-status',
as: 'callStatus',
localField: 'appointmentUpdateReason',
foreignField: '_id',
unwinding: { preserveNullAndEmptyArrays: true }
},
totalDocsExamined: Long("3669"),
totalKeysExamined: Long("3849"),
collectionScans: Long("0"),
indexesUsed: [ '_id_' ],
nReturned: Long("3850"),
executionTimeMillisEstimate: Long("23902")
},
{
'$sort': {
sortKey: { date: -1, slotStartTime: -1, userName: 1 },
limit: Long("10")
},
totalDataSizeSortedBytesEstimate: Long("109465"),
usedDisk: false,
spills: Long("0"),
nReturned: Long("10"),
executionTimeMillisEstimate: Long("23902")
},
{
'$project': {
_id: true,
updatedAt: true,
state: true,
photo: true,
records: true,
slotStartTime: true,
date: true,
facilityPractitioner: true,
updatedBy: true,
paymentOrder: true,
consultationType: true,
createdAt: true,
prescription: true,
user: { _id: true, name: true },
practitioner: { _id: true, name: true },
callStatus: { code: true }
},
nReturned: Long("10"),
executionTimeMillisEstimate: Long("23902")
}
],
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' } }
}
},
{ '$match': {} },
{
'$lookup': {
from: 'users',
localField: 'userId',
foreignField: '_id',
as: 'user'
}
},
{
'$unwind': { path: '$user', preserveNullAndEmptyArrays: true }
},
{
'$lookup': {
from: 'practitioners',
localField: 'practitioner',
foreignField: '_id',
as: 'practitioner'
}
},
{
'$unwind': { path: '$practitioner', preserveNullAndEmptyArrays: true }
},
{
'$lookup': {
from: 'facility-practitioners',
localField: 'facilityPractitioner',
foreignField: '_id',
as: 'facilityPractitioners'
}
},
{
'$unwind': {
path: '$facilityPractitioners',
preserveNullAndEmptyArrays: true
}
},
{
'$addFields': {
userName: {
'$toLower': {
'$concat': [ '$user.name.first', ' ', '$user.name.last' ]
}
},
photo: '$user.profile.picture'
}
},
{
'$match': {
'$and': [
{
'practitioner._id': ObjectId("64f97e5d7884cee0c928a44c")
},
{
'$or': [
{
state: {
'$in': [
'CANCELLED',
'FULLFILLED',
'UN_ATTENDED',
'USER_NO_SHOW',
'PRACTITIONER_NO_SHOW',
'NETWORK_ERROR',
'INCOMPLETE'
]
}
}
]
}
]
}
},
{
'$lookup': {
from: 'master-data-call-status',
localField: 'appointmentUpdateReason',
foreignField: '_id',
as: 'callStatus'
}
},
{
'$unwind': { path: '$callStatus', preserveNullAndEmptyArrays: true }
},
{ '$sort': { date: -1, slotStartTime: -1, userName: 1 } },
{
'$project': {
_id: 1,
prescription: 1,
paymentOrder: 1,
user: { name: 1, _id: 1 },
photo: 1,
practitioner: { name: 1, _id: 1 },
consultationType: 1,
date: 1,
slotStartTime: 1,
facilityPractitioner: 1,
records: 1,
createdAt: 1,
updatedAt: 1,
state: 1,
updatedBy: 1,
callStatus: { code: 1 }
}
},
{ '$skip': 0 },
{ '$limit': 10 }
],
cursor: {},
'$db': 'test_tuning'
},