Hi - I’ve recently completed M220 & M121. Excellent courses. I recommend both.
I have several aggregation pipelines I’ve developed. In general, they are slow and one exceeds 100MB available storage when I configure the settings a certain way.
I really like how you can run explain() on find queries and get the execution time in milliseconds. I’d like to do the same for aggregation, but I can’t make that work. Any suggestions?
Thanks - Peter.
Hi @Peter_Winer,
You can run generate Explain Results for an aggregate operation using the mongo or mongosh shell as follows:
// Find
db.collection.find({ ... }).explain(<verbosity>);
// Aggregate
db.collection.explain(<verbosity>).aggregate([ ... ]);
Note that you can explain a find operation in the same fashion if you choose:
db.collection.explain(<verbosity>).find({ ... });
The <verbosity> above indicates one of the three available verbosity modes: queryPlanner, executionStats and allPlansExecution.
I’m not sure what you mean by that - do you mean 100MBs RAM usage? I explain how that works here.
Unless your pipelines spill to disk or are writing an output file they shouldn’t be that slow. Can you give some examples of pipelines that are slow? If you are using MongoDB version 4.4 then explain with executionStats will show how long each stage took as well as how many documents entered each stage.
Example:
db.coll.explain("executionStats").aggregate( [ <your-pipeline-here> ])
I walk through an example of this at the end of the talk I did for last year’s MDB.live event but unfortunately the resolution of the code didn’t turn out that good…
Asya
Asya - Thank you for responding. My database is hosted on MongoDB Atlas with version 4.2.13 Enterprise. Clearly I should upgrade to 4.4. The stats you referenced will be very helpful.
The most important collection is a set of roughly 1.4MM timed events, growing about 25K events per day. I’m implementing a dashboard that shows various views of the events, including time-based distributions.
Users can configure the duration of the time series and how many divisions per day. The most fine grained option is 40 days of data, divided into 1-hour divisions, or 960 ‘buckets’ total.
The $group stage is problematic. I use $addFields to give each event a ‘bucket number’, then group by bucket number. So, I am grouping the events into 960 separate buckets. I’m very interested to know the time spent on this stage, and looking forward to trying 4.4 to discover this.
I wonder if using $bucket would be more efficient? I could calculate the bucket boundaries in JavaScript and compose the $bucket stage. Then I would use $bucket to assign each event to the proper bucket, based on the time stamp, which is indexed. Does that make sense?
I would be happy to share one or more of my pipelines, if you would review them. LMK and I’ll send a couple of examples over. Best - Peter.
Alex - Thank you for replying.
I tried explain() as you recommended. It seems like the problem is with my MongoDB version, as pointed out by Asya in the next message of this thread. I am using 4.2.13 Enterprise hosted on MongoDB Atlas. I will upgrade to 4.4 in the next available time-window. This should help my effort significantly.
Best - Peter.
If you post the full pipeline that’s slow, we can take a look and make suggestions about where it can be optimized. Of course having 4.4 explain output would be more enlightening but even 4.2 explain output will show some useful details about how the pipeline is executing and how it’s using indexes (if any).
Asya
Asya - I updated to 4.4.4, also updated my local mongo shell and node.js driver. I now have execution stats available and i’m finding areas to improve. In a few days after I’ve fixed the ‘low hanging fruit’, I’d like to take you up on your offer and send a couple of complete pipelines. Thank you! Peter.
.explain does not working on vscode Is .explain works only on Terminal
.explain does not working on vscode Is .explain works only on Terminal
how to make it work on vs code