Q: I want to use Studio 3T’s Visual Explain on an aggregation but I don’t see any timings after the initial query. What’s wrong?
Welcome to the latest Studio 3T Ask Manatees Anything – #Studio3T_AMA. Want to know how to use Studio 3T to make your MongoDB life better? Just tweet your question using the #Studio3T_AMA hashtag or email your query to [email protected].
A: What we’re talking about here is the output of the Explain tab in the Aggregation Editor. If you’ve never opened the Explain tab on a find query or an aggregation, you are missing out. When shown, the Explain tab takes the output of MongoDB’s explain() function and translates it into a usable, visual chart. And you have the option to run that query and harvest the run time stats for that query, stage by stage.
Earlier this year, we extended the reach of Visual Explain to include the Aggregation tool. For most users it works fine, but for some, there is an issue. It’ll show them the query plan and aggregation stages quite happily. However, when they click the “Run Full Explain” light bulb icon what they can see looks something like this:
The most likely reason you’ll see this is because you are running MongoDB pre version 4.4. Before MongoDB 4.4, the explain() operation when applied to an aggregation returned only the statistics related to the initial query itself. The rest of the output from explain was muted about how long it took to run or how much it processed, leading to this rather anemic output.
The fix is to upgrade your database to MongoDB 4.4 or later. If you are on Atlas, it should be auto-upgrading and already be on 4.4. If you are running MongoDB locally, on-premises or self-hosted, you’ll have to work through the manual upgrade steps to get yourself up to date.
When you’ve done that and come back to the aggregation, go to Explain, click Run Full Explain and the result now looks like this:
Now we can see how many documents passed through the pipeline and how long each stage took. Check out that 5 second lookup stage, if we double click on it we’ll be able to see the details of that stage and its timings. And we can check that we’re working with indexed fields in that foreign collection (spoiler: yes we are). The incoming and outgoing arrows on that stage show 28,000 documents passing through; a lot of separate lookups which explains the time taken.
Visual Explain for Aggregations gives you a quick way to figure out which stage is taking time so you can optimize your aggregation or choose a different strategy.
Related reading: MongoDB Explain, Visualized
