Count with aggregation lookup takes too long

Hi, I am using Mongo shell version v5.0.8

I am trying to count the documents, using aggregation pipeline with 3 lookups, in which the count is taking around 50s.

My question is, when we use the count we are not fetching the documents, then why it takes so much time and how can we reduce it?

Looking forward for your reply.

As requested by @K.V in your related thread please

In addition, I am worry about a use-case where you need 3 lookups to perform a count. A little bit more explanation might help us figure out what is the issue. Samples documents from your collections might also be useful.

Size of data compared to system characteristics is also important to know.

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.

As already mentioned, please

and

The vast majority of the execution time estimated by the explain plan output (looking at the executionTimeMillisEstimate fields) is running the $lookup stages. While counting documents doesn’t return the documents, MongoDB does still have to access all of those documents to evaluate the $match or other filter stages. In that case, counting is not going to be significantly faster than returning all matching documents since most of the execution time is spent running the $lookup stages.

You may be able to speed up your query by filtering the “joined” dataset as early as possible to reduce the execution time of the subsequent $lookup stages or by moving some of the “joined” documents into the "bookings" documents so you don’t have to run a $lookup at all.

1 Like