Video Transcript (English)

After you build your aggregation pipeline, you will want to assess its performance. For example, how do you know if it uses indexes as expected? 

That’s where an explain plan comes in.

In this lesson, we’ll talk about explain plans. We'll discuss what it is and how it can teach you more about your pipeline’s execution and identify opportunities for optimization. 

We’ll do this by taking a look at an example in the MongoDB Shell and Compass

When building aggregation pipelines, we want to identify inefficiencies, optimize queries by adjusting indexes, and restructure or refine pipelines for better performance.

An explain plan is one of the primary tools we use to accomplish this.

An explain plan in MongoDB is a detailed report that gives us information about possible plans for executing a query, including the winning plan that MongoDB selects.

By default, it includes details about each stage of the query execution process, like index usage, the path data takes through collections, and the resources required for execution. Explain plans provide this information not only for the winning plan but also for the rejected plans.

MongoDB gives us a couple of options for viewing an explain plan.

First, we can use the mongosh method for explain, chained with the aggregation method like in this example, to return the explain plan.

When we run it, it returns detailed information about how our pipeline is executed as JSON. 

The output contains a lot of information. To learn more about all of the information included in an explain plan and how to adjust the level of detail provided by the explain plan, visit MongoDB’s documentation.

For this video, the queryPlanner and executionStats fields are a helpful place to start.

The queryPlanner field provides information about the winning plan selected by the query optimizer, or in other words, how MongoDB will execute a given query.

The executionStats field provides details about the winning plan execution, including execution times, number of documents examined, etc.

By analyzing these fields, we can better understand the efficiency of queries, identify potential performance bottlenecks, and make informed decisions about query optimization and indexing strategies.

We can also use MongoDB Compass to view and interact with the explain plan.

Compass provides UI features to help us gain quick insights from an explain plan. Let’s take a look at an example to learn more!

For this example, we’ll use an aggregation pipeline on the books collection to analyze trends and determine which book genre received the highest user ratings over the last five years.

Let’s look at this aggregation pipeline in Compass.

First, we’ll use the $match stage to find all book documents that were published within the last 5 years.

Then we use $group to group documents by genre and calculate the average rating for the genre.

And finally we use the $sort stage to sort in descending order to show the top genres by average rating.

In the sample output, we can see that documents include the genre and average rating, with the highest rated genre, Dystopian, appearing first.

To view the explain plan for this pipeline, we click on the explain button. 

On the explain plan page, we see the visual tree, which is a simplified way to view information from the queryPlanner and executionStats fields.

If we want to read the full output, we can select Raw output. Now we can view info about the plan for executing our pipeline under queryPlanner.

And executionStats for information about how the pipeline was actually executed.

If we want a high-level overview of how our query was executed, we can look at the Query Performance Summary section. Let’s review this to see if we can improve our query.

Here, it’s indicating that we have no index available for our query and that it’s performing a collection scan. That tells me that we should add an index to improve our query performance.

$group stages cannot use an index and, in this case, our $sort stage can’t either since it is sorting data that has already been processed by the pipeline. However, our $match stage can use an index. 

Since we are filtering by publication date, we can add an index to the date_of_original_publication field to support our query and avoid a costly collection scan.

After adding an index, the Explain Plan Visual Tree shows an index scan which means we are effectively using the index and examining fewer documents. 

Nice work! Let’s recap what we covered in this lesson.

An explain plan in MongoDB is a detailed report that gives us information about the execution of a query.

We can use an explain plan to learn more about index usage, the path data takes through collections, and the resources required for execution, and more!

You can view an explain plan by using the explain() mongosh method when you run your aggregation pipeline, or by viewing the output in MongoDB Compass.

Finally, we looked at an example in compass and learned that the statistics and insights provided by an explain plan make it an important tool for testing and optimizing your aggregation pipeline.