Video Transcript (English)

After you create your indexes, you will want to see if they’re being used as expected.

That’s where an explain plan comes in.

In this video, we’ll talk about explain plans. We'll discuss what an explain plan is and how it can teach you more about how your indexes are impacting query performance.

We’ll do this by taking a look at an example in the MongoDB Shell with our Bank messaging App.

An explain plan in MongoDB is a detailed report that provides information about how the database executes a query.

The explain() method has three verbosity levels:

queryPlanner, which is the default, executionStats, and allPlansExecution.

The verbosity level determines how much information is included in the explain plan.

Depending on the verbosity level, an explain plan can include details about each stage of the query execution process, like index usage, the path data takes through collections, and the resources consumed for execution.

Explain plans can provide this information not only for the winning plan but also for the rejected plans if the verbosity is set to allPlansExecution.

Let’s use the explain method to see if the indexes we created for our bank messaging app are being used as expected.

We can view an explain plan for each of our queries by adding the explain plan method when we run the query. Here we’ve added it to our first query and we’ve set the verbosity level to executionStats.

This will return an explain plan document in the terminal that will include execution stats for the winning plan.

Explain plans can include a lot of information.

Let's focus on a few key fields to confirm index usage and query performance for our three queries.

Here, we’re looking at a portion of the explain plan for query 1.

In this demo, we are running MongoDB 8.0. If you are using a different version, the explain plan might look a little different.

On the left is explain plan output from before we added the index, and on the right is explain plan output from after we added the index.

We’re looking at the winning plan field. This field describes the selected execution plan for the query. It helps you understand whether and how indexes are utilized.

On the left, we can see that our query performed a collection scan, meaning that it didn’t use an index. And on the right, we can see that it performed an index scan and here we can see the name of the index we created. Great! That means that it's being used as intended.

Now let’s evaluate performance.

Next, let’s look at the execution stats field. This field provides detailed runtime statistics about the execution of the query and we can see how many documents were examined and returned.

On the left we see that we examined one hundred five thousand nine hundred and fifteen documents and returned twenty three documents. That’s not a great ratio!

But on the right, we see that, using the index, we examined twenty three documents, and returned twenty three documents - that’s a huge improvement!

Additionally, we see that the execution time decreased from sixty five millisecends to 0!

Great! It looks like the first index is supporting the first query as intended.

Now, let’s run our second query with the explain() method using the same verbosity level: executionStats.

When we take a look at the explain plan for the second query, we see that it is also supported by the index we created. And under execution stats, we see the same improvement to performance - great!

Finally, let’s run query three with the explain() method.

Under the winning plan section we see that we again went from performing a collection scan on the left to performing an index scan after adding the second index. Here we can see the name of the second index.

Once again, under execution stats, we see that we have gone from examining one hundred five thousand documents to only 19. And the execution time went from 73 miliseconds to 0!

That’s great! So all of our indexes are being used by our queries and now you’ve seen their performance impact.

But there is one more thing that we can do to boost performance with indexes whenever possible: cover our query.

A covered query in MongoDB is a query where all the fields that are queried and returned are covered by a single index.

This means that MongoDB can satisfy the query entirely, only using the index, without needing to read the collection documents themselves.

Covered queries are highly efficient because they eliminate the need for MongoDB to read documents, which reduces I/O operations and improves performance.

To demonstrate how this works, if we add the following projection to query two, the query would only need to return values for the user_id, timestamp and category.

Remember - those values are stored in the leaf nodes of the b + tree for this index, so we don't need to access any documents to return the queried values.

If we run the explain() method on this updated query, we can see under the winningPlan section that the projection is covered.

And when we look at the executionStats, we see that no documents were examined!

That means that the query was fulfilled by using the values stored in the index.

As the messages collection grows, this can have a significant impact on the performance of this query.

Whenever possible, design your indexes to cover queries.

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

We showed you how to view an explain plan for each of our queries with the explain method.

We used the explain plan to determine if our queries were using indexes as intended. We also viewed Explain Plan execution stats for the winning plan to confirm that performance was improved.

Finally, we learned that when a query is covered, MongoDB can satisfy the results of that query just by using the index.