Video Transcript (English)

So, we’ve talked about how to optimize write operations - what about reads? Remember that optimizing reads keeps your application fast, efficient, and scalable. This can save resources, cut costs, and result in higher user satisfaction.

To keep reads as performant as possible, MongoDB has the query planner. It determines the specific path of a query and which indexes to use.

But, there may be times when you want to decide how a query is executed. In those cases, we can use query settings to override the query planner’s chosen plan and optimize our queries.

In this video, you’ll learn how to use specific query settings via the setQuerySettings command. Let’s get started!

First, we’ll start by talking about the query planner.

This component, within the MongoDB database, is responsible for determining the most efficient way to execute queries. It’s generally effective at this task, but there are some scenarios where it might not choose the best plan for a query shape. This can cause performance issues, especially as queries become more complex and the size of the dataset grows.

Remember, a "query shape" in MongoDB is a blueprint or structure that groups queries sharing similar attributes, such as filters , sorts, projections, aggregation pipeline stages, and the namespace, which is the combined database and collection name.

Queries with a similar structure but different values are considered to have the same query shape.

So what do we do if the query planner doesn’t choose the best plan for a query shape?

One option is to use hints to influence the query planner to use a specific index when executing a query. Hints are great for many use cases, but they come with some drawbacks. When hints aren't the right tool for our application, we can use query settings.

For example, if a query is embedded in an application, we would have to modify the query to include a hint.

Second, a single query shape may be executed with different parameters such as filtering by different values. To add hints to every instance of that query shape, you’d have to apply hints to all similar queries across your codebase separately as seen here.

Applying hints individually to all of these queries inflates the workload and creates redundant code.

Additionally, adding hints typically requires updating the application code and then redeploying the application, which can be time-consuming and impractical.

And finally, hints are query-specific and do not persist across the cluster or survive application changes unless explicitly coded for each query. So using hints does not always work, depending on your application needs.

Query settings are a good alternative, because they persist at the server level, remain in effect across cluster nodes, and even continue after database restarts.

To use query settings, we use the db dot adminCommand, a MongoDB Shell method that lets us run administrative commands at the server level.

Let’s use db.adminCommand to execute the setQuerySettings and removeQuerySettings commands.

We’ll start with setQuerySettings. This command lets us control how the query planner handles specific query types based on their shape. It works with the find, distinct, and aggregate commands.

Let’s take a look at the syntax.

First, in the setQuerySettings document's fields section, we specify the type of operation the settings apply to.

operation the settings apply to.

We provide the command type as the field and the collection name as the value. Here we specify a find. Then we add the query filter and any other necessary information along with the database name. This setQuerySettings document is the only required data. Everything after this is optional.

In the settings section, we provide the settings for index hints.

This includes the NS field where we specify the namespace for the hints. The namespace specifies the database and collection.

In the allowedIndexes values, we list the indexes that the query can use.

Outside of the settings section, we have two more optional fields.

queryFramework allows us to specify the query framework that we wish to use between the classic framework and the slot based execution framework. For more details on these frameworks and how they work, check out the MongoDB documentation.

Finally, the reject option, if set to true, forces the server to reject any queries that match this specific shape, and throws an error that the query was rejected. This is set to false by default.

Before we make any adjustments to query settings, it’s a good practice to first analyze the query using the explain() method. This will allow us to check whether the optimal index is being used or if no index is being used, and identify possible areas of concern.

After that, we can use the setQuerySettings command to experiment with execution settings for that particular query shape.

This helps test the performance of hints without changing application code. Once we find a good solution, we can keep the settings, knowing they will stay even after a server restart. Let’s look at a few examples of setQuerySettings.

A typical application for setQuerySettings is forcing the use of a specific index.

For example, here we are applying settings for a simple query that matches on homes that we have full use of and that don't require a minimum stay of more than 3 nights.

For this query shape, we want to make sure that it uses this index covering the entire filter.

Sometimes, rejecting certain indexes may be a better option than forcing a specific one. We can also do that with the setQuerySettings command by excluding those indexes from our allowed indexes field.

For example, here we provide three indexes that are allowed, but we didn't include the index that we specified previously.

After making changes with setQuerySettings, we can use the explain() method again to check for improvements. Look for stages like IXSCAN to confirm efficient index use and check indexName to ensure the correct index is used.

We can also validate performance improvements by checking the ratio between total keys examined and total docs examined as well as the overall query time via execution time millis.

If specific query settings are no longer needed, maybe due to new business requirements or changes in our query patterns, they can be removed to revert to the default query planner behavior by using the removeQuerySettings command.

There are two ways to remove query settings: by providing the query shape or by using the query shape hash.

Removing query settings via a query shape is straightforward, but does require that we know the shapes we are using. Then, we simply supply the shape to the removeQuerySettings command, as seen here.

Alternatively, we can supply the queryShapeHash value for the settings. We can use the aggregation framework to list the current query settings and find the queryShapeHash associated with a specific query shape.

To find all of the query settings in place, we can use this statement.

The output will include documents where each document represents a query shape, its settings, and the queryShapeHash.

We can then copy that hash value, and paste it into our removeQuerySettings command in place of the query shape.

Nice work! In this video, you learned how to apply specific query settings for a query shape using the setQuerySettings command. This will help improve query performance when the query planner doesn't pick the best plan.

You also learned how to remove the query settings when they are no longer necessary with the removeQuerySettings command.

With this information, you can take a systematic approach to optimizing your query performance in MongoDB.