Thank you @steevej for reply.
The explain stats for query is:
/* 1 */
{
"explainVersion" : "1",
"stages" : [
{
"$cursor" : {
"queryPlanner" : {
"namespace" : "demo.bookings",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"arrival_date_time" : {
"$lte" : 1672223546.0
}
},
{
"status" : {
"$not" : {
"$in" : [
3.0,
6.0,
7.0,
8.0,
9.0
]
}
}
}
]
},
"queryHash" : "FDA0AD43",
"planCacheKey" : "D5670236",
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"stage" : "PROJECTION_DEFAULT",
"transformBy" : {
"_id" : 1,
"address.address" : 1,
"address.apt" : 1,
"address_id" : 1,
"checklist_pictures.booking_id" : 1,
"checklist_pictures.tasks.subtask.photo_urls" : 1,
"job_pictures.booking_id" : 1,
"provider_ids" : 1,
"uid" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"arrival_date_time" : -1
},
"memLimit" : 104857600,
"type" : "default",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"status" : 1,
"arrival_date_time" : 1,
"end_date_timestamp" : 1,
"is_visible" : 1
},
"indexName" : "status_1_arrival_date_time_1_end_date_timestamp_1_is_visible_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : [],
"arrival_date_time" : [],
"end_date_timestamp" : [],
"is_visible" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[MinKey, 3.0)",
"(3.0, 6.0)",
"(6.0, 7.0)",
"(7.0, 8.0)",
"(8.0, 9.0)",
"(9.0, MaxKey]"
],
"arrival_date_time" : [
"[-inf.0, 1672223546.0]"
],
"end_date_timestamp" : [
"[MinKey, MaxKey]"
],
"is_visible" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION_DEFAULT",
"transformBy" : {
"_id" : 1,
"address.address" : 1,
"address.apt" : 1,
"address_id" : 1,
"checklist_pictures.booking_id" : 1,
"checklist_pictures.tasks.subtask.photo_urls" : 1,
"job_pictures.booking_id" : 1,
"provider_ids" : 1,
"uid" : 1
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"arrival_date_time" : -1
},
"memLimit" : 104857600,
"type" : "default",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"status" : 1,
"arrival_date_time" : -1
},
"indexName" : "status_1_arrival_date_time_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : [],
"arrival_date_time" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[MinKey, 3.0)",
"(3.0, 6.0)",
"(6.0, 7.0)",
"(7.0, 8.0)",
"(8.0, 9.0)",
"(9.0, MaxKey]"
],
"arrival_date_time" : [
"[1672223546.0, -inf.0]"
]
}
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 7960,
"executionTimeMillis" : 1406,
"totalKeysExamined" : 7966,
"totalDocsExamined" : 7960,
"executionStages" : {
"stage" : "PROJECTION_DEFAULT",
"nReturned" : 7960,
"executionTimeMillisEstimate" : 46,
"works" : 15928,
"advanced" : 7960,
"needTime" : 7967,
"needYield" : 0,
"saveState" : 25,
"restoreState" : 25,
"isEOF" : 1,
"transformBy" : {
"_id" : 1,
"address.address" : 1,
"address.apt" : 1,
"address_id" : 1,
"checklist_pictures.booking_id" : 1,
"checklist_pictures.tasks.subtask.photo_urls" : 1,
"job_pictures.booking_id" : 1,
"provider_ids" : 1,
"uid" : 1
},
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 7960,
"executionTimeMillisEstimate" : 24,
"works" : 15928,
"advanced" : 7960,
"needTime" : 7967,
"needYield" : 0,
"saveState" : 25,
"restoreState" : 25,
"isEOF" : 1,
"docsExamined" : 7960,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "SORT",
"nReturned" : 7960,
"executionTimeMillisEstimate" : 6,
"works" : 15928,
"advanced" : 7960,
"needTime" : 7967,
"needYield" : 0,
"saveState" : 25,
"restoreState" : 25,
"isEOF" : 1,
"sortPattern" : {
"arrival_date_time" : -1
},
"memLimit" : 104857600,
"type" : "default",
"totalDataSizeSorted" : 525360,
"usedDisk" : false,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 7960,
"executionTimeMillisEstimate" : 3,
"works" : 7967,
"advanced" : 7960,
"needTime" : 6,
"needYield" : 0,
"saveState" : 25,
"restoreState" : 25,
"isEOF" : 1,
"keyPattern" : {
"status" : 1,
"arrival_date_time" : 1,
"end_date_timestamp" : 1,
"is_visible" : 1
},
"indexName" : "status_1_arrival_date_time_1_end_date_timestamp_1_is_visible_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : [],
"arrival_date_time" : [],
"end_date_timestamp" : [],
"is_visible" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[MinKey, 3.0)",
"(3.0, 6.0)",
"(6.0, 7.0)",
"(7.0, 8.0)",
"(8.0, 9.0)",
"(9.0, MaxKey]"
],
"arrival_date_time" : [
"[-inf.0, 1672223546.0]"
],
"end_date_timestamp" : [
"[MinKey, MaxKey]"
],
"is_visible" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 7966,
"seeks" : 7,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
}
}
},
"nReturned" : NumberLong(7960),
"executionTimeMillisEstimate" : NumberLong(73)
},
{
"$lookup" : {
"from" : "users",
"as" : "customer_info",
"localField" : "uid",
"foreignField" : "_id",
"unwinding" : {
"preserveNullAndEmptyArrays" : false
}
},
"totalDocsExamined" : NumberLong(7960),
"totalKeysExamined" : NumberLong(7960),
"collectionScans" : NumberLong(0),
"indexesUsed" : [
"_id_"
],
"nReturned" : NumberLong(7960),
"executionTimeMillisEstimate" : NumberLong(319)
},
{
"$lookup" : {
"from" : "users",
"as" : "provider_info",
"localField" : "provider_ids",
"foreignField" : "_id"
},
"totalDocsExamined" : NumberLong(7897),
"totalKeysExamined" : NumberLong(8356),
"collectionScans" : NumberLong(0),
"indexesUsed" : [
"_id_"
],
"nReturned" : NumberLong(7960),
"executionTimeMillisEstimate" : NumberLong(669)
},
{
"$lookup" : {
"from" : "users_address",
"as" : "address",
"localField" : "address_id",
"foreignField" : "_id",
"unwinding" : {
"preserveNullAndEmptyArrays" : true
}
},
"totalDocsExamined" : NumberLong(0),
"totalKeysExamined" : NumberLong(0),
"collectionScans" : NumberLong(0),
"indexesUsed" : [],
"nReturned" : NumberLong(7960),
"executionTimeMillisEstimate" : NumberLong(905)
},
{
"$addFields" : {
"full_address" : {
"$toLower" : [
{
"$concat" : [
"$address.apt",
{
"$const" : " "
},
"$address.address"
]
}
]
}
},
"nReturned" : NumberLong(7960),
"executionTimeMillisEstimate" : NumberLong(905)
},
{
"$lookup" : {
"from" : "booking_job_pictures",
"as" : "job_pictures",
"localField" : "_id",
"foreignField" : "booking_id"
},
"totalDocsExamined" : NumberLong(5),
"totalKeysExamined" : NumberLong(5),
"collectionScans" : NumberLong(0),
"indexesUsed" : [
"booking_id"
],
"nReturned" : NumberLong(7960),
"executionTimeMillisEstimate" : NumberLong(1148)
},
{
"$lookup" : {
"from" : "checklist_pictures",
"as" : "checklist_pictures",
"localField" : "_id",
"foreignField" : "booking_id",
"unwinding" : {
"preserveNullAndEmptyArrays" : true
}
},
"totalDocsExamined" : NumberLong(0),
"totalKeysExamined" : NumberLong(0),
"collectionScans" : NumberLong(0),
"indexesUsed" : [],
"nReturned" : NumberLong(7960),
"executionTimeMillisEstimate" : NumberLong(1350)
},
{
"$match" : {
"$or" : [
{
"job_pictures.booking_id" : {
"$exists" : true
}
},
{
"$and" : [
{
"checklist_pictures.booking_id" : {
"$exists" : true
}
},
{
"checklist_pictures.tasks.subtask.photo_urls" : {
"$exists" : true
}
},
{
"checklist_pictures.tasks.subtask.photo_urls.photo_url" : {
"$exists" : true
}
},
{
"checklist_pictures.tasks.subtask.photo_urls.photo_url" : {
"$not" : {
"$eq" : ""
}
}
}
]
}
]
},
"nReturned" : NumberLong(5),
"executionTimeMillisEstimate" : NumberLong(1350)
},
{
"$group" : {
"_id" : {
"$const" : null
},
"count" : {
"$sum" : {
"$const" : 1.0
}
}
},
"maxAccumulatorMemoryUsageBytes" : {
"count" : NumberLong(72)
},
"totalOutputDataSizeBytes" : NumberLong(229),
"usedDisk" : false,
"nReturned" : NumberLong(1),
"executionTimeMillisEstimate" : NumberLong(1350)
}
],
"serverInfo" : {
"host" : "iron-System-Product-Name",
"port" : 27017,
"version" : "5.0.8",
"gitVersion" : "c87e1c23421bf79614baf500fda6622bd90f674e"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : 104857600,
"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
"internalQueryProhibitBlockingMergeOnMongoS" : 0,
"internalQueryMaxAddToSetBytes" : 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
},
"command" : {
"aggregate" : "bookings",
"pipeline" : [
{
"$match" : {
"status" : {
"$nin" : [
3.0,
9.0,
6.0,
7.0,
8.0
]
},
"arrival_date_time" : {
"$lte" : 1672223546.0
}
}
},
{
"$sort" : {
"arrival_date_time" : -1.0
}
},
{
"$lookup" : {
"from" : "users",
"localField" : "uid",
"foreignField" : "_id",
"as" : "customer_info"
}
},
{
"$unwind" : "$customer_info"
},
{
"$lookup" : {
"from" : "users",
"localField" : "provider_ids",
"foreignField" : "_id",
"as" : "provider_info"
}
},
{
"$lookup" : {
"from" : "users_address",
"localField" : "address_id",
"foreignField" : "_id",
"as" : "address"
}
},
{
"$unwind" : {
"path" : "$address",
"preserveNullAndEmptyArrays" : true
}
},
{
"$addFields" : {
"full_address" : {
"$toLower" : {
"$concat" : [
"$address.apt",
" ",
"$address.address"
]
}
}
}
},
{
"$lookup" : {
"from" : "booking_job_pictures",
"localField" : "_id",
"foreignField" : "booking_id",
"as" : "job_pictures"
}
},
{
"$lookup" : {
"from" : "checklist_pictures",
"localField" : "_id",
"foreignField" : "booking_id",
"as" : "checklist_pictures"
}
},
{
"$unwind" : {
"path" : "$checklist_pictures",
"preserveNullAndEmptyArrays" : true
}
},
{
"$match" : {
"$or" : [
{
"job_pictures.booking_id" : {
"$exists" : true
}
},
{
"$and" : [
{
"checklist_pictures.booking_id" : {
"$exists" : true
}
},
{
"checklist_pictures.tasks.subtask.photo_urls" : {
"$exists" : true
}
},
{
"checklist_pictures.tasks.subtask.photo_urls.photo_url" : {
"$exists" : true,
"$ne" : ""
}
}
]
}
]
}
},
{
"$group" : {
"_id" : null,
"count" : {
"$sum" : 1.0
}
}
}
],
"cursor" : {},
"$db" : "demo"
},
"ok" : 1.0
}
This query is executed on the sample data with comparatively less docs and it is taking 1.5s to execute.