Скачать или смотреть Optimizing MongoDB Aggregation: How to Speed Up $lookup Performance

Discover effective strategies to improve the performance of MongoDB's `$lookup` operation in aggregations, addressing slow query speeds and improving efficiency.
---
This video is based on the question https://stackoverflow.com/q/66491181/ asked by the user 'Changkun' ( https://stackoverflow.com/u/3819460/ ) and on the answer https://stackoverflow.com/a/66501200/ provided by the user 'Joe' ( https://stackoverflow.com/u/2282634/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: $lookup in aggregation is very slow

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Optimizing MongoDB Aggregation: How to Speed Up $lookup Performance

When working with MongoDB, developers often use the $lookup stage in aggregation pipelines to join documents from different collections. However, as data grows, you may notice that the performance of these operations starts to suffer. A common issue reported by many users is that $lookup can significantly slow down queries. In this guide, we will discuss why this happens and how to address these performance issues effectively.

Understanding the Problem: Slow $lookup Operations

In the scenario described, a MongoDB user noted that using $lookup resulted in slow query performance—taking over 500 ms for a single lookup. This lag was evident during the aggregation process, particularly due to the way MongoDB retrieves documents from collections. Here are the relevant details from their collection sizes:

Visit Collection Size: 8174 documents

Links Collection Size: 89 documents

When executing the aggregation with $lookup, MongoDB was performing a full collection scan (COLLSCAN) on the visit collection, leading to slower performance. The following execution stats were observed:

Total documents examined: 8174

Execution time: 643 ms

Given this scenario, you might wonder why the query is so slow and how to improve it.

Why is $lookup Slow?

The crux of the issue lies in the lack of filtering at the initial stage of the aggregation pipeline. Without any filters, MongoDB fetches all documents from the visit collection. Each document in visit is then processed with the $lookup stage, which performs a find query on the links collection for each document.

Key Factors Contributing to The Slow Query

No Filtering: The absence of filters in the first stage forces MongoDB to examine all documents in the visit collection.

Full Collection Scan: Each $lookup operation performs a read against the links collection, potentially requiring a full scan if no index exists.

Document Examination Overhead: Each duplicate search through the links collection (a total of 727,486 examinations) adds significant overhead.

Solutions to Improve $lookup Performance

To enhance the performance of your $lookup operations, consider implementing one or more of the following strategies:

1. Create an Index on the Foreign Key

Creating an index on the alias field in the links collection can significantly reduce the overhead caused by document examinations.

How It Helps: This index will allow MongoDB to quickly find the match instead of doing a full scan of the links documents each time an alias is processed.

Implementation: You can create the index using the following command:

[[See Video to Reveal this Text or Code Snippet]]

2. Integrate Data Instead of Using $lookup

Instead of maintaining a separate links collection that requires a lookup, consider denormalizing your data structure by directly embedding link information in the visit document.

Benefits: This approach eliminates the need for a $lookup entirely during read operations.

Drawbacks: While this will speed up retrieval, it may complicate writes if you need to update link information frequently, as you'll need to update it in each visit document as well.

3. Apply Filtering Before $lookup

If applicable, add stages before $lookup to filter the visit documents that need aggregation. This reduces the number of documents processed at the $lookup stage and, consequently, the number of document examinations.

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

In summary, slow $lookup operations in MongoDB aggregations can largely be mitigated through indexing, data integration, and pre-filtering stages. By understanding the reasons behind the performance bottleneck and implementing