MongoDB lookup query optimization

I spent most of my day yesterday optimizing one query to MongoDB. The query is an aggregation pipeline with two very similar $lookup sections which use “Join Conditions and Uncorrelated Sub-queries” as opposed to localField/foreignField equality match. Every $lookup sub-query used conditions that implied that it will use an index. However, we encountered situation when this query became super-slow as the DB size was growing. And the speed issues were surely not due to DB size alone, because it was still quite small.

I started with the explain() tool in the Mongo shell but quickly found out the first pitfal: explain does not report whether an index is used on the $lookups. I was almost sure it doesn’t so I re-wrote the query using localField/foreignField match, which luckily was possible, though a bit tricky given that I wanted the query behavior to stay completely intact.

Performance increased vastly, from nearly 9s to split second! The purpose of this post is to warn you that $lookups in Mongo can cause serious performance problems, and sometimes it’s impossible to clearly see what is happening because Mongo’s explain() doesn’t explain everything.